Skip site navigation (1) Skip section navigation (2)

MO: SQL Query Performance tips

From: Michael Ossareh <michael(dot)ossareh(at)12snap(dot)com>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: MO: SQL Query Performance tips
Date: 2004-12-06 21:06:16
Message-ID: 5CCF98464386D4119F1200306E0050CC01447AE0@betauk.london.12snap.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

pgsql-sql by date

Next:From: Greg StarkDate: 2004-12-06 21:28:54
Subject: Re: Making dirty reads possible?
Previous:From: Tom LaneDate: 2004-12-06 18:47:00
Subject: Re: Set Returning Function problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group