MySQL排名查询和左加入
问题描述:
我有一个数据库用户和facebook_accounts,属于用户。用户按照与其他用户相关的点数“排列”:这是通过嵌入式SELECT语句完成的,该语句用比用户多得多的点数来计算所有用户。MySQL排名查询和左加入
该数据库拥有〜10k个用户。下面的SQL查询需要的MySQL〜0.16s满足:
SELECT
*, (SELECT (COUNT(*) + 1)
FROM users AS UserHigher
WHERE UserHigher.points > User.points
) AS rank
FROM
users AS User
ORDER BY
User.points DESC, User.created ASC
LIMIT 0, 30
然而,加入LEFT JOIN也检索用户的facebook_account挂起MySQL的:
SELECT
*, (SELECT (COUNT(*) + 1)
FROM users AS UserHigher
WHERE UserHigher.points > User.points
) AS rank
FROM
users AS User
LEFT JOIN
facebook_accounts AS FacebookAccount
ON (FacebookAccount.user_id = User.id)
ORDER BY
User.points DESC, User.created ASC
LIMIT 0, 30
据我所知,COUNT()选择排序用户的方法效率不高,但这是我遇到的最可靠的方法。我不明白的是,为什么一个简单的LEFT JOIN会破坏一个合理的查询,当它看起来与排名SELECT语句完全分离时。
有什么建议吗?
答
我的猜测是原始查询首先执行排序,并且只执行排名30次。第二个查询太复杂了,MySQL无法检测到这种优化。
下可能会有所帮助:
select *
from (SELECT *, (SELECT (COUNT(*) + 1)
FROM users AS UserHigher
WHERE UserHigher.points > User.points
) AS rank
FROM users AS User
ORDER BY User.points DESC, User.created ASC
LIMIT 0, 30
) t join
facebook_accounts AS FacebookAccount
ON (FacebookAccount.user_id = User.id)
order by points desc, created asc
+0
你必须是正确的,左连接必须破坏LIMIT提供的优化。你的查询也解决了这个问题 - 谢谢! – 2013-02-23 00:50:02
你对这些列的索引? 'EXPLAIN'告诉你什么? – 2013-02-23 00:24:41
不要每次都读取所有数据。 – symcbean 2013-02-23 00:44:04
所有表都有唯一的每行数字索引,而FacebookAccounts中的user_id是foriegn键列。 这是两个查询的EXPLAIN的屏幕截图。 https://dl.dropbox.com/u/225179/temp/sql-queries.png 看起来JOIN强制PRIMARY用户表使用“临时”,所以也许从限制0,30的优化在那里丢失 – 2013-02-23 00:46:06