Complex query making site extremely slow

Question

 select SQL_CALC_FOUND_ROWS DISTINCT media.*, username 
from album as album, album_permission as permission, user as user, media as media , word_tag as word_tag, tag as tag 
where ((media.album_id = album.album_id and album.private = 'yes' and album.album_id = permission.album_id and (permission.email = '' or permission.user_id = '') ) or (media.album_id = album.album_id and album.private = 'no' ) or media.album_id = '0' ) 
and media.status = '1' 
and media.user_id = user.user_id 
and word_tag.media_id = media.media_id 
and word_tag.tag_id = tag.tag_id 
and tag.name in ('justin','bieber','malfunction','katherine','heigl','wardrobe','cinetube') and media.media_type = 'video' 
and media.media_id not in ('YHL6a5z8MV4') 
group by media.media_id 
order by RAND()
#there is limit too, by 20 rows..

i dont know where to begin explaining about this query, but please forgive me and ask me if you have any question. following is the explanation.

SQL_CALC_FOUND_ROWS is calculating how many rows are there and will be using for pagination, so it counts total records, even tho only 20 is showing.

DISTINCT will stop the repeated row to display.

username is from user table.

album, album_permission. its checking if album is private and if it is, then check if user has permission, by user_id.

i think rest is easy to understand, but if you need to know more about it, then please ask.

im really frustrated by this query and site is very slow or not opening sometimes cause of this query. please help

SQL query: EXPLAIN select SQL_CALC_FOUND_ROWS DISTINCT media.*, username from album as album, album_permission as permission, user as user, media as media , word_tag as word_tag, tag as tag where ((media.album_id = album.album_id and album.private = 'yes' and album.album_id = permission.album_id and (permission.email = '' or permission.user_id = '') ) or (media.album_id = album.album_id and album.private = 'no' ) or media.album_id = '0' ) and media.status = '1' and media.user_id = user.user_id and word_tag.media_id = media.media_id and word_tag.tag_id = tag.tag_id and tag.name in ('justin','bieber','malfunction','katherine','heigl','wardrobe','cinetube') and media.media_type = 'video' and media.media_id not in ('YHL6a5z8MV4') group by media.media_id order by RAND() ;
Rows: 6
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  permission  system  album_id  NULL  NULL  NULL  1  Using temporary; Using filesort
1  SIMPLE  album  ALL  PRIMARY  NULL  NULL  NULL  68   
1  SIMPLE  word_tag  ALL  media_id  NULL  NULL  NULL  88383  Using where; Using join buffer
1  SIMPLE  media  eq_ref  media_id,album_id  media_id  34  _site.word_tag.media_id  1  Using where
1  SIMPLE  tag  eq_ref  PRIMARY  PRIMARY  4  _site.word_tag.tag_id  1  Using where
1  SIMPLE  user  eq_ref  PRIMARY  PRIMARY  34  _site.media.user_id  1   

table structure http://pastie.org/912388 this link has table dump, which you can do in phpmyadmin or run it and see the structure of tables.

Answer 1

The very best thing you can do is run the query, but put an EXPLAIN in front of it, and analyze the results.

Without knowing the structure (especially the indexes) of all of the tables in that query, we'll be hard-pressed to give you a good answer.

Start with this: Optimizing Queries with EXPLAIN.

If you can't make any headway by following the suggestions there, then post the structures of your tables and we can probably help you from there.

Answer 2

Have you tried removing the 'ORDER BY RAND()' to see if that makes a difference?

Answer 3

I'd start by ditching the ORDER BY RAND(). That's a huge performance-killer. See http://www.paperplanes.de/2008/4/24/mysql_nonos_order_by_rand.html and http://jan.kneschke.de/projects/mysql/order-by-rand/.

I'd suggest doing the randomization in your code, after you've selected the results.

Answer 4

Disclaimer: I normally work with SQL Server not MySQL and this is the first time I have attempted to interpet a MySQL Explain plan! In particular I am assuming that it should be read from top to bottom. Please someone correct me if I'm wrong!

But If I have understod it correctly it is saying for the word_tag table that it has 88383 rows. There was one possible index it could have chosen (possible_keys = media_id) but it actually chose not to use an index at all (key = null).

The reason for that appears to be that the only thing in the query that could restrict the number of rows being returned from word_tag is the join word_tag.media_id = media.media_id and word_tag.tag_id = tag.tag_id but neither media nor tag are looked at until later. Can you tell me how many rows both of those tables have?

Edit: Actually 34 and 4 rows are shown in EXPLAIN. What if you add an index on word_tag containing both columns tag_id and media_id? If that doesn't change the plan maybe try running Analyze Table on these tables http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html

Also.

1. How many rows does the result set return if you remove the LIMIT (i.e. what is the value of SQL_CALC_FOUND_ROWS)?

2. What about if you remove the DISTINCT? How many rows then?

Answer 5

There are some discussions going on about the performance impact of using SQL_CALC_FOUND_ROWS:

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Test your query disabling this option and see if it makes a difference (in performance). If it does, you might need to find a different way of finding out how many rows the result set has.