如何使用嵌套SELECT来优化此SQL查询？ [英] How can I optimize this SQL query with nested SELECT's?

问题描述

我有以下查询：

SELECT src_big, created, modified, owner, aid, caption 
FROM photo 
WHERE aid IN (SELECT aid, modified FROM album WHERE owner IN (SELECT uid2 FROM friend WHERE uid1=me() or uid2 = me())order by modified desc) 
ORDER BY created DESC 
LIMIT 30

这样运行速度很慢，我相信因为嵌套的SELECT等。我如何使这个表演更快？

This runs pretty slow, and I'm sure because of the nested SELECT's etc. How can I make this perform quicker? How should it be rewritten to be better optimized?

推荐答案

尝试使用联接而不是SubQuery，它更快：

try to use joins instead of SubQuery it is faster:

SELECT photo.src_big, photo.created, photo.modified, photo.owner, photo.aid, 
photo.caption FROM photo 
inner join album on album.aid = photo.aid 
inner join  friend on album.owner = friend.uid2 
WHERE uid1=me() or uid2 = me()
order by modified desc,created DESC LIMIT 30

注意：您需要将表名

这篇关于如何使用嵌套SELECT来优化此SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！