我研究了其他问题并尝试了他们的解决方案,但没有一个对我有帮助。如何降低该查询的执行时间?
SELECT c.id,c.title,c.time,u.username, (SELECT time FROM comments_read as io WHERE io.id=c.id AND io.uid=$logged_userid LIMIT 1) as read_time
FROM comments as c
LEFT JOIN users u ON u.uid=c.uid
WHERE c.deleted=0
ORDER by c.time DESC
LIMIT 20
这是您的查询:
SELECT c.id, c.title, c.time, u.username,
(SELECT io.time
FROM comments_read as io
WHERE io.id = c.id AND
io.uid = $logged_userid
LIMIT 1
) as read_time
FROM comments as c LEFT JOIN
users u
ON u.uid = c.uid
WHERE c.deleted = 0
ORDER by c.time DESC
LIMIT 20;
索引可以用来提高性能。我将从comments(deleted, time, uid, id)
, users(uid)
和comments_read(id, uid, time)
开始。
注意:通常不鼓励使用LIMIT
而不使用ORDER BY
,因为结果不稳定。你可以得到任何匹配的行。
如果上面的索引没有帮助,他们可能被JOIN
混淆了。你可以做同样的事情:
SELECT c.id, c.title, c.time,
(SELECT u.username FROM users u WHERE u.uid = c.uid) as username,
(SELECT io.time
FROM comments_read as io
WHERE io.id = c.id AND
io.uid = $logged_userid
LIMIT 1
) as read_time
FROM comments as c
WHERE c.deleted = 0
ORDER by c.time DESC
LIMIT 20;
这应该利用上面的索引,并且没有"文件排序"步骤来减慢速度。