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

Answer 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.

Answer 2

You have three major issues here:

1. FIND_IN_SET.

   It's not sargable, an index cannot make it faster. Create a many-to-many relationship table (or tables).

2. 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)

3. 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

Answer 3

• 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.

Answer 4

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.