一道很常見的 MySQL 面試題,你能一眼看出來那個語句執行效率最好嗎？

有兩張表 user 使用者表 1000萬 資料和 thread 帖子表 5000萬 資料，寫一條 SQL 語句查詢前十名最多發帖的使用者名稱和帖子數。

user

thread

SQL1

SELECT username,count(*) tc FROM `user` LEFT JOIN thread ON `user`.uid = thread.uid GROUP BY  thread.uid ORDER BY  tc DESC limit 0,10 ; 

SQL2 :

SELECT username,tc FROM `user` INNER JOIN  (SELECT uid,count(*) tc FROM thread GROUP BY uid ORDER BY tc DESC limit 0,10 ) AS td ON `user`.uid = td.uid ; 

SQL3 :

SELECT username, tc FROM `user` LEFT JOIN  (SELECT uid, count(*) tc FROM thread GROUP BY uid ORDER BY tc DESC limit 0,10 ) AS td ON `user`.uid = td.uid limit 0,10 ; 

本作品採用《CC 協議》，轉載必須註明作者和本文連結