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
