Re: SQL Query Performance tips

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 weaponsOne 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 cntFROM weaponsWHERE weapon_level < (user_level)    AND weapon_cost = 0GROUP 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 cntFROM weapons w, user_weapons uwWHERE w.weapon_id = uw.weapon_id    AND
uw.user_id= (the user_id)    AND w.weapon_cost > 0GROUP 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 cntFROM weaponsWHERE weapon_level < (user_level)    AND weapon_cost = 0GROUP BY
weapon_alignment)
UNION ALL
SELECT w.weapon_alignment, count(1) as cntFROM weapons w, user_weapons uwWHERE w.weapon_id = uw.weapon_id    AND
uw.user_id= (the user_id)    AND w.weapon_cost > 0GROUP BY weapon_alignment)
 
GROUP BY weapon_alignment;
You can also do this :

SELECT weapon_alignment, count(1) as cnt FROM
(SELECT weapon_alignmentFROM weaponsWHERE weapon_level < (user_level)    AND weapon_cost = 0)
UNION ALL
SELECT w.weapon_alignmentFROM weapons w, user_weapons uwWHERE 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 ?