一道很常见的 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 协议》，转载必须注明作者和本文链接