Date: Sat, 15 Jan 2005 09:26:22 +0100
To: "Michael Ossareh" <michael.ossareh@12snap.com>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: SQL Query Performance tips
References: 
 <5CCF98464386D4119F1200306E0050CC01447BE6@betauk.london.12snap.com>
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <opskmxp8pwth1vuj@musicbox>
In-Reply-To: 
 <5CCF98464386D4119F1200306E0050CC01447BE6@betauk.london.12snap.com>
User-Agent: Opera M2/7.54 (Linux, build 751)


	If I understand well a person has all the free weapons which have a level  
<= to his own level, and of course all the weapons he bought.

	1) get da weapons
	One query can only use one index. Bad for you !
	Let's split the free and non-free weapons.

	1a) free weapons
SELECT weapon_alignment, count(1) as cnt
	FROM weapons
	WHERE weapon_level < (user_level)
		AND weapon_cost = 0
	GROUP BY weapon_alignment;

	No need for distinct anymore ! Note also that distinct'ing on weapon_name  
is a slower than on weapon_id.
	You can create an index on (weapon_cost,weapon_level) but I don't think  
it'll be useful.
	For ultimate speed, as this does not depend on the user_id, only the  
level, you can store the results of this in a table, precalculating the  
results for all levels (if there are like 10 levels, it'll be a big win).

	1b) weapons bought by the user

SELECT w.weapon_alignment, count(1) as cnt
	FROM weapons w, user_weapons uw
	WHERE w.weapon_id = uw.weapon_id
		AND uw.user_id = (the user_id)
		AND w.weapon_cost > 0
	GROUP BY weapon_alignment;

	You'll note that the weapons in 1a) had cose=0 so they cannot appear  
here, no need to distinct the two.

	2) combine the two

SELECT weapon_alignment, sum(cnt) FROM
(SELECT weapon_alignment, count(1) as cnt
	FROM weapons
	WHERE weapon_level < (user_level)
		AND weapon_cost = 0
	GROUP BY weapon_alignment)
UNION ALL
SELECT w.weapon_alignment, count(1) as cnt
	FROM weapons w, user_weapons uw
	WHERE w.weapon_id = uw.weapon_id
		AND uw.user_id = (the user_id)
		AND w.weapon_cost > 0
	GROUP BY weapon_alignment)
GROUP BY weapon_alignment;

	You can also do this :

SELECT weapon_alignment, count(1) as cnt FROM
(SELECT weapon_alignment
	FROM weapons
	WHERE weapon_level < (user_level)
		AND weapon_cost = 0)
UNION ALL
SELECT w.weapon_alignment
	FROM weapons w, user_weapons uw
	WHERE w.weapon_id = uw.weapon_id
		AND uw.user_id = (the user_id)
		AND w.weapon_cost > 0)
GROUP BY weapon_alignment;

	How does it turn out ?