🔒 Locked

SQL SELECT Optimisation

Started by Evil Steve Jun 19, 2007 at 8:18 AM 0 replies 700+ views

Original Post

382

Jun 19, 2007 at 8:18 AM

Hi all, I'm currently trying to write some forum software, and I've come across something that looks like it could be done a lot more cleanly and probably faster. The three relevant tables are forum_list, which contains a list of all the forums, the categories they're in, and the "group_view" column. It's either zero (All groups can view thew forum), or a group ID number. Next, there's the user_group_users table, which is just a list of user IDs (user_id column) and group IDs (group_id column), for determining which users are in which groups. Finally, there's the user_groups table, which has information about user groups such as the group name, ID (group_id column), and the group moderator (group_mod_id column). So, here's my query for selecting all forums in category 0, which user ID 999 is allowed to view:


SELECT * FROM forum_list WHERE category_id='0' AND (group_view='0' OR
   group_view IN (SELECT group_id FROM user_group_users WHERE user_id='999') OR
   group_view IN (SELECT group_id FROM user_groups WHERE group_mod_id='999')
);

The two nested SELECTs seems pretty horrible to me, and that's the main thing that I think can be optimised out somehow, I just don't know how... I'm pretty much a SQL novice, the last time I played with non-basic SELECTs was the 2nd year of my degree, which was 3 years ago [smile] I'm using MySQL 5.0.21 if it matters.

Xai

344

Xai

Jun 19, 2007 at 8:51 AM

often subselects can be rewritten as joins, especially in cases like yours which are actually the standard use of joins.

Also, joins themselves have 2 syntaxes - the syntax that most people learned first, where you just list the tables you are selecting from and then the where clauses, aka:

select ... from a, b where a.x = x2 and b.y = y2

or the explicit join syntax:

select ... from a inner join b on a.x = b.x where a.y = y2

in the second version (the one I ALWAYS use), you always join on whatever the matching foreign keys are in your db ... then you always put the extra contraints in the where clause at the end ...

select  forum_list.*from  forum_listleft outer join  user_group_users on user_group_users.group_id = forum_list.group_viewleft outer join  user_groups on user_groups.group_id = forum_list.group_viewwhere  forum_list.category_id = '0'  and  (    user_group_users.user_id = '999'    or    user_groups.mod_id = '999'  )

the key here is not increased efficiency or anything, it is the fact that you write the database join rules in the joins, and they stay correct for a long long long time, then you write the specific logic for this particular query in the where clause.

you can save the join part of the query as a view, and then just do a select like :

select
...
from
forum_groups_and_users
where
...

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.