Complex sql that is very slow when the query is conditionally ordered
I have the following complex query that I need to use. When I run it, it takes 30 to 40 seconds. But if I remove the order by clause it takes 0.0317s to return the result, which compares very quickly to 30s or 40s.
select DISTINCT media.*
, username
from album as album
, album_permission as permission
, user as user, media as media
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.user_id = user.user_id
and media.media_type = 'video'
order by media.id DESC
LIMIT 0,20
The id order by
is the primary key, which is also indexed. So I don't know what the problem is.
I also have an album and album permission table to check if the media is public or private, if closed then check if the user has permission or not. I thought maybe this is causing the problem. What if I did it in a subquery, would it work better? Also can someone help me write this sub query if this is a solution? If you can't write it, at least tell me. I am really going crazy with this problem.
MAYBE SOLUTION
Yes, I think a query like this would be the best solution for this, because the next query takes 0.0022 seconds. But I'm not sure if the album validation will be accurate or not, please check.
select media.*, username
from media as media
, user as user
where media.user_id = user.user_id
and media.media_type = 'video'
and media.id in
(select media2.id
from media as media2
, album as album
, album_permission as permission
where ((media2.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.album_id = media2.album_id )
order by media.id DESC
LIMIT 0,20
a source to share
Use EXPLAIN to determine why it is faster without an ORDER BY clause:
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
I would recommend rewriting the query with ANSI join syntax. This can help performance. Something like that:
select DISTINCT media.*
, username
from album as album
inner join media as media on media.album_id = album.album_id
inner join user as user on media.user_id = user.user_id
left outer join album_permission as permission on album.album_id = permission.album_id
where ((album.private = 'yes'
and (permission.email = '' or permission.user_id = '') )
or album.private = 'no'
or media.album_id = '0' )
and media.media_type = 'video'
order by media.id DESC
LIMIT 0,20
You can also split the request into 2 or 3 different requests and concatenate them together to avoid outer join.
a source to share