complex sql which runs extremely slow when the query has order by clause

Question

I have following complex query which 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.0317 sec to return the result, which is really fast compare to 30 sec or 40.

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 on order by is primary key which is indexed too. So I don't know what is the problem.

I also have album and album permission table, just to check if media is public or private, if private then check if user has permission or not. I was thinking maybe that is causing the issue. What if I did this in sub query, would that work better? Also can someone help me write that sub query, if that is the solution? If you can't help write it, just at least tell me. I'm really going crazy with this issue..

SOLUTION MAYBE

Yes, I think sub-query would be best solution for this, because the following query runs at 0.0022 seconds. But I'm not sure if validation of an album would 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   

Answer 1

Use EXPLAIN to determine why it's faster without the 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 too. This might help the performance. Something like this:

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 could also separate the query into 2 or 3 different queries and UNION them together to avoid the outer join.