MO: SQL Query Performance tips

Hi All,

I have a partial table structure as below

tbl_user_main
user_id pk
user_level references tbl_level
user_class references tbl_class
user_level references tbl_level

tbl_level
level_id pk
level_name
level_points

tbl_weapon
weapon_id pk
weapon_name
weapon_level references tbl_level
weapon_class references tbl_class
weapon_alignment references tbl_alignment
weapon_cost

tbl_class
class_id pk
class_name

tbl_alignment
alignment_id pk
alignment_name

tbl_user_weapon
user_id references tbl_user
weapon_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