Обсуждение: SQL Query Performance tips

Hi All,

I have a partial table structure as below

tbl_user_mainuser_id pkuser_level references tbl_leveluser_class references tbl_classuser_level references tbl_level

tbl_levellevel_id pklevel_namelevel_points

tbl_weaponweapon_id pkweapon_nameweapon_level references tbl_levelweapon_class references tbl_classweapon_alignment
referencestbl_alignmentweapon_cost 

tbl_classclass_id pkclass_name

tbl_alignmentalignment_id pkalignment_name

tbl_user_weaponuser_id references tbl_userweapon_id references tbl_weapon


I want to know how many weapons a given user has of a particular class or
alignment where the level of the weapons are equal or less than the players
own level. For this I have developed the following SQL:

1 | select alignment.alignment as alignment,
count(distinct(weapon.weapon_name)) as count from
2 |     ( select * from tbl_alignment where alignment_id != 1 ) alignment,
3 |     ( select * from tbl_weapon) weapon,
4 |     ( select * from tbl_user_main where user_id = $user_id ) person
5 | where weapon.weapon_id IN (
6 |     select u.weapon_id
7 |     from tbl_weapon u
8 |     where u.weapon_level <= person.user_level
9 |     and u.cost = 0
10|     or u.weapon_id IN (
11|         select uu.weapon_id
12|         from tbl_user_weapon uu
13|         where uu.user_id = person.user_id
14|     )
15| )
16| and alignment.alignment_id = weapon.weapon_alignment
17| group by alignment.alignment
18| order by alignment.alignment ASC;

To clarify lines 5 through 15 - a weapon can be free or cost some amount. To
track users that have bought a weapon there is the tbl_user_weapon table.
Every purchase gets listed in there. The count must take into account all
free weapons and weapons which the user has purchased. At the moment there
are some 300 weapons.

In the case above the "alignment" with id 1 is a catchall so I disregard it.
$user_id can be any user_id from tbl_user_main. The result of a query such
as this is along the lines of;

alignment | count
----------+-------
Shadow    | 4
Heavenly  | 6

This takes a long time to complete - circa 3 seconds. Which is fine when run
one off - but it appears in a section of a website that will potentially be
accessed a lot and I can see it causing a few issues in terms of table/row
locking as the game app uses these tables a lot. ideally I need it to run a
lot quicker. Can anyone see any ways to speed this up?

I have considered views but these seem to just be a way of aliasing a query
as opposed to the materialised views present in other RDBMS's. Short of
creating another 3rd form table that has

user_id | alignment_id | count

Which would get updated upon each weapon purchase I cannot see a low
overhead way of getting the data. Creating a table such as this would need
to be a last resort as its maintenance will quickly become a headache due to
the number of purchase routes in the project.

Any help is much appreciated.

Thanks,

------------------------------
Michael Ossareh (M²)
Technical Manager
12snap UK Ltd

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 ?

Hi PFC

Thanks for this! It has sped up complete - now in fact there is no delay!

A few tweaks had to be made to the code ; here it is:

select breakdown.alignment, sum(cnt) as num FROM
( (select alignment.name as class, count(1) as cntfrom weapons,alignmentwhere weapons.level < (select level_id from
userswhere uid =
 
$userid)and cost = 0and alignment.id = weapons.align_idgroup by alignment.name)
UNION ALL
select b.class as class, count(1) as cntfrom weapons w, user_weapons uu, alignment bwhere tu.weaponid = uu.weaponidand
uu.user_id= ($userid)and b.id = tu.idgroup by b.class ) as breakdown
 
group by breakdown.class;

Essentially you had skipped a few brackets and I had forgotten to note one
of the tables. It works!

Thanks a lot!!

Mike