Complex process of creating requests
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 don't know where to start explaining this request, but please forgive me and ask me if you have any questions. following explanation.
SQL_CALC_FOUND_ROWS calculates how many rows exist and will be used for pagination, so it counts total records even while only 20 is shown.
DISTINCT will stop displaying the repeated line.
username from the user table.
album, album_permission. its checking if the album is private and if so then check if the user has permission, user_id.
I think rest is easy to understand, but if you need to know more about it, please ask.
im really disappointed with this request and the site is very slow or not opening sometimes the reason for this request. 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 a table dump that you can do in phpmyadmin or run it and see the table structure.
source to share
The best thing you can do is run a query, but put EXPLAIN
and parse the results in front of it .
Without knowing the structure (especially the indexes) of all the tables in this query, it will be difficult for us to give you a good answer.
Start with this: Optimizing Queries with EXPLAIN .
If you cannot succeed following the guidelines, post your table structures and we can probably help you.
source to share
I would start to run ORDER BY RAND (). This is a huge 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 suggest doing randomization in your code after you select the results.
source to share
Disclaimer: I usually work with SQL Server, not MySQL and this is the first time I tried using the 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 understand it correctly, it says in the table word_tag
that it has 88383 rows. There was one possible index he could have chosen (maybe_keys = media_id), but actually decided not to use the index at all (key = null).
The reason for this is that the only thing in the query that can limit the number of rows returned from word_tag is the concatenation word_tag.media_id = media.media_id and word_tag.tag_id = tag.tag_id
, but neither media
is nor is it scrolled tag
through. Can you tell me how many rows both of these tables have?
Edit: Actually 34 and 4 lines are shown in EXPLAIN. What if you add an index on word_tag
that contains both columns tag_id
and media_id
? If that doesn't change the plan, try running the analysis table on these tables http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
Besides.
-
How many rows does the result set return if you drop the LIMIT (i.e. what is the value of SQL_CALC_FOUND_ROWS)?
-
How about removing DISTINCT? How many lines then?
source to share
There are some discussions about the impact of performance on usage 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 by disabling this option and see if it makes a difference (in performance). If so, you may need to find another way to find out how many rows the result set has.
source to share