Mysql查询，使用ORDER BY时运行缓慢

Question

我有一个查询运行缓慢，当使用ORDER BY子句。

包含最大行数的表。30.000行。

结果是10行，大约需要0.2秒。

不带"ORDER BY id DESC"需要0.01秒。

我已经研究了其他关于堆栈的帖子，并尝试优化这个脚本，但我的知识已经达到了极限。

我还创建了所有列连接的索引，WHERE, ORDER by.

你认为有什么可以改进的，或者关于如何通过返回更快的结果来订货的想法?

Mysql是这样的

SELECT  *
FROM  
( SELECT  posts.id, posts.createdDateTime, posts.caption, posts.postUrl,
posts.isVideo, posts.paymentType, posts.postPrice, profile.name,
profile.username, 
( SELECT  COUNT(*)
FROM  likes
where  likes.postIdFK=posts.id
) as likes, 
( SELECT  COUNT(*)
FROM  likes
where  likes.postIdFK=posts.id
AND  likes.userIdFK=21643
) as myLike, 
( SELECT  COUNT(*)
FROM  comments
where  comments.postIdFK=posts.id
AND  comments.deleted=0
) as comments
FROM  posts
JOIN  users AS profile  ON posts.userIdFK=profile.id
WHERE  posts.deleted=0
AND  profile.deleted=0
AND  ( posts.userIdFK IN (
SELECT  subscribedToProfileIdFK
FROM  
( SELECT  userSubscriptions.subscribedToProfileIdFK, max(monthlySubscriptions.endSubscription) endSubscription
FROM  userSubscriptions
LEFT JOIN  monthlySubscriptions  ON monthlySubscriptions.userSubscriptionId=userSubscriptions.id
WHERE  userSubscriptions.userIdFK=21643
AND  userSubscriptions.quickpayComplete=1
GROUP BY  userSubscriptions.id 
) t
WHERE  (t.endSubscription >= CURDATE())
GROUP BY  t.subscribedToProfileIdFK )
OR  posts.id IN (
SELECT  paidPosts.postIdFK
FROM  paidPosts
WHERE  paidPosts.userIdFK=21643 
)
OR  posts.userIDFK=21643 ) 
) AS row
ORDER BY  id DESC
LIMIT  0,10

EXPLAIN看起来像这样

<表类>idselect_type表类型possible_keys键key_lenref行额外tbody><<tr>1主要概要文件ref,删除删除1const5410使用临时的;使用filesort1主要文章refuserIdFK,删除userIdFK4admin_slc.profile。id1使用在8物化paidPosts所有userIdFK, postIdFK空空空5使用在6物化所有空空空空14使用在7派生userSubscriptions指数userIdFK, quickpayComplete主要4空15使用在7派生monthlySubscriptionsrefuserSubscriptionIduserSubscriptionId4admin_slc.userSubscriptions.id15相关子查询评论refpostIdFK,删除postIdFK4admin_slc.posts。id1使用在4相关子查询喜欢refuserIdFK, postIdFKpostIdFK4admin_slc.posts。id1使用在3相关子查询喜欢refpostIdFKpostIdFK4admin_slc.posts。id1使用指数

您完全可以重写这个查询以从select子句中删除select查询，如下所示:

SELECT * FROM ( 
SELECT posts.id, 
posts.createdDateTime,  
posts.caption,  
posts.postUrl,  
posts.isVideo,  
posts.paymentType,  
posts.postPrice, 
profile.name,  
profile.username,
l.cnt as likes,
ml.cnt as myLike,
comm.cnt as comments -- as comments
FROM posts 
JOIN users AS profile ON posts.userIdFK=profile.id
LEFT JOIN (SELECT likes.postIdFK, COUNT(*) as cnt FROM likes group by likes.postIdFK) as l 
on l.postIdFK=posts.id
LEFT JOIN (SELECT likes.postIdFK, COUNT(*) as cnt FROM likes where likes.userIdFK=21643 group by likes.postIdFK) ml 
on ml.postIdFK=posts.id
LEFT JOIN (SELECT comments.postIdFK, COUNT(*) as cnt FROM comments where comments.deleted=0 group by comments.postIdFK) as comm 
on comm.postIdFK=posts.id
WHERE posts.deleted=0 
AND profile.deleted=0
AND ( posts.userIdFK IN (
SELECT subscribedToProfileIdFK 
FROM (SELECT userSubscriptions.subscribedToProfileIdFK,
max(monthlySubscriptions.endSubscription) endSubscription
FROM userSubscriptions 
LEFT JOIN monthlySubscriptions ON monthlySubscriptions.userSubscriptionId=userSubscriptions.id 
WHERE userSubscriptions.userIdFK=21643 
AND userSubscriptions.quickpayComplete=1
GROUP BY userSubscriptions.id) t
WHERE (t.endSubscription >= CURDATE())
-- GROUP BY t.subscribedToProfileIdFK -- this group by is not needed
)
OR exists 
(SELECT 1 
FROM paidPosts 
WHERE paidPosts.userIdFK=21643
AND posts.id = paidPosts.postIdFK)
OR posts.userIDFK=21643 
)
) AS row
ORDER BY id DESC
LIMIT 0,10