Complex query making site extremely slow
- by Basit
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
