mysql - Query 10 times slower using LEFT JOIN CASE in order by -

mysql - Query 10 times slower using LEFT JOIN CASE in order by -

i have relatively long query (posted below reference).

i have tried debug why query slow (2 seconds), , have found cause.

in end of query do:

order -- order date date(p.date) desc, -- order followed people case when n.id null '0' else '1' end desc -- case takes query 20ms 2 seconds

if remove case order by, it'll execute in around 20 ms.

why this?

when run query explain, notice case add together "using temporary" field.

see explain queries below:

explain query case when n.id null '0' else '1' end desc in order by

explain query without case when n.id null '0' else '1' end desc in order by

the total query (if may help): select -- feed type '1' feed_type, -- fetch post info p.id, p.receiver, p.date, p.message, p.system_msg, p.type post_type, -- fetch author info u.user_id, u.firstname, u.lastname, u.type, u.permalink, av.file avatar_file, -- fetch receiever info u2.user_id receiver_user_id, u2.firstname receiver_firstname, u2.lastname receiver_lastname, u2.permalink receiver_permalink, u2.type receiver_type, -- fetch post comment count ( select count(*) edu_posts pc pc.comment = p.id , pc.deleted null ) commentcount, -- fetch post count ( select count(*) edu_likes l l.like_entity = p.id ) likecount, -- user follow state case when n.id not null '1' else '0' end is_following, -- check if user likes post case when l.like_id not null '1' else '0' end user_likes edu_posts p inner bring together -- author info edu_users u on u.user_id = p.author left bring together -- author avatar edu_avatars av on av.fk = p.author , av.temp = 0 , av.fk_type = 1 left bring together -- receiver info (if any) edu_users u2 on u2.user_id = p.receiver left bring together -- check if author/receiver followed current user edu_notification_list n on n.user = 1 , n.following = 1 , ( n.fk = p.author or n.fk = p.receiver ) , ( ( n.type = 5 , p.type = 3 ) or ( n.type = 2 , p.type = 1 ) ) left bring together -- check if user likes post edu_likes l on l.like_entity = p.id , l.like_author = 1 p.deleted null , p.comment null , ( p.id = p.comment or 1 = 1 ) , ( n.id not null or p.system_msg = 0 ) order -- order date date(p.date) desc, -- order followed people case when n.id null '0' else '1' end desc limit 20 offset 0

note: please allow me know, if see of other tables.

you perform operation can't create utilize of indexes. instead try

order date(p.date) desc, n.id null asc

mysql sql query-optimization