Optimize this SQL Query

Question

This SQL query disgusts me. I didn't write it, but it's a massive cause of issues on our servers. I'm willing to split it up into multiple queries and do some of the processing via PHP (like, the RAND()).

$sql = "SELECT a.code, a.ad_id, a.position, a.type, a.image, a.url, a.height, a.width
    FROM " . AD_TABLE ." a, " . USER_GROUP_TABLE . " g
    WHERE (a.max_views >= a.views OR a.max_views = '0')
    AND (FIND_IN_SET(" .$forum_id. ", a.show_forums) > 0 OR a.show_all_forums = '1')
    AND g.user_id = " . $user->data['user_id'] . "
    AND FIND_IN_SET(g.group_id, a.groups)
    AND FIND_IN_SET(" . $user->data['user_rank'] . ", a.ranks)
    AND a.start_time < " . time() . "
    AND a.end_time > " . time() . "
    AND (a.clicks <= a.max_clicks OR a.max_clicks = '0')
    ORDER BY rand()";

Yeesh, I feel icky after pasting that...

EDIT: Below is the results of the "EXPLAIN" on a sample query in the above format, comma delimited:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","g","ref","user_id","user_id","3","const",6,"Using temporary; Using filesort"
1,"SIMPLE","a","ALL","max_views","","","",10,"Using where"

It is

Are you asking a question, or trying to get some SQL optimised without paying for it? — Mathew
– Mathew, Commented Jun 30, 2009 at 14:31
Would you mind doing an EXPLAIN SELECT and post the results? — alexn
– alexn, Commented Jun 30, 2009 at 14:34
Change your join condition from a find-in-set to a 'like' syntax and try it. instead of :find-in-set(g.group_id, a.groups) try : a.groups like '%'. g.group_id . '%' — blispr
– blispr, Commented Jun 30, 2009 at 15:08

Quassnoi · Accepted Answer · 2009-06-30 14:41:11Z

6

You have three major issues here:

FIND_IN_SET.

It's not sargable, an index cannot make it faster. Create a many-to-many relationship table (or tables).
- a.start_time < GETDATE() AND a.end_time > GETDATE()
MySQL is not good in optimizing that. You can keep you timespans as geometry boxes and create a SPATIAL INDEX over them, this will be much faster (though less readable)
- ORDER BY RAND()
If you are using this to sample data (i. e. you don't need all rows but rather a small random subset), there is a more efficient way to do this, described in this article in my blog:
- Selecting random rows

answered Jun 30, 2009 at 14:41

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

blispr · Accepted Answer · 2009-06-30 14:50:44Z

4

You do not have an explicit join in this query between table a and table g : they are only related by find_in_set (g.group_id, a.groups)
How large is your set in "a.groups" , i.e. does the csv string contain one group-id most of the times?
If 99% of the cases contain only 1 group, then make a foreach loop over "a.groups" in php and execute a real join (or probably it might eliminate your user_group_table altogether) from the query.
- For the minority of cases where you have more than 1 group membership, the query will still perform OK with an explicit join.

This will add some more code in your php class/function.

answered Jun 30, 2009 at 14:50

blispr

9125 silver badges11 bronze badges

1 Comment

blispr Over a year ago

Ok, how about one more try. This could be very simple . Use "and a.groups like " . "%" . g.group_id . "%" This will evaluate to "and 'grp1,grp2,grp4' like '%grp1%' " and you will get the hit.

northpole · Accepted Answer · 2009-06-30 14:31:19Z

1

I am guessing your problem lies with the dates.

AND a.start_time < " . time() . "
AND a.end_time > " . time() . "

I would try putting indexes on these fields and see if that helps. Comparing dates causes the database to compare to each row in the table.

answered Jun 30, 2009 at 14:31

northpole

10.3k7 gold badges38 silver badges60 bronze badges

Comments

Tom H · Accepted Answer · 2010-07-08 19:10:45Z

0

If you execute this a lot.. try and use bind variables (instead of string concatenation) .. so the query does not have to be parsed every single time..

edit: Sorry..didn't see the MYSQL tag. Unless it's changed recently I don't think MySQL likes prepared statements. ORACLE on the other hand LOVES them.

edited Jul 8, 2010 at 19:10

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

answered Jun 30, 2009 at 16:02

ShoeLace

3,6063 gold badges33 silver badges44 bronze badges

Collectives™ on Stack Overflow

Optimize this SQL Query

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related