mysql - 如何使用嵌套 SELECT 优化此 SQL 查询？

Question

我有以下查询:

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 等。我怎样才能让它执行得更快？应该如何重写才能更好的优化？

Answer 1

尝试使用联接而不是子查询，它更快:

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

注意:表名需要放在最后