I try to make a list of users having the most articles published (or something like that).
So I wrote something like this:
SELECT u.id, COUNT(a.id) AS articles
FROM users u
LEFT JOIN articles a ON a.user_id = u.id
GROUP BY u.id
ORDER BY articles DESC
LIMIT 10
The 'explain' command shows it 'Using join buffer (Block Nested Loop)' and 'Using temporary; Using filesort'.
It costs really long time to get the result. I know the reason that causes this problem. But I don't know how to solve it.
Also, I wanted to sort records by user's register time instead of published articles as an option.
And I know I can add a column to users table accumulates the articles number, but I'm not be able to change the original program.
It seems to me that this is the only way to make the list. But also the impossible way to retrieve the result before my time.
Is there another faster way to get a top 10 list based on join queries?
Any suggestion would be appreciated.
Try this:
SELECT
u.id, t.articles
FROM USERS u
LEFT JOIN
(
SELECT user_id, COUNT(id) AS `articles`
FROM articles
GROUP BY user_id
) t ON u.id = t.user_id
ORDER BY t.articles DESC
LIMIT 10
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加