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
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With