A very common MySQL interview question, can you see at a glance which statement is the most efficient?

Time:2021-4-17

There are two tablesuserUser tables 10 million data andthreadPost table 50 million data, write oneSQLStatement to query the top ten most posted user names and posts.

user

uid username password create_at
Primary key testnamne testpass time

thread

tid uid title contents create_at
Primary key General index title content time

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 ; 

This work adoptsCC agreementReprint must indicate the author and the link of this article

Gao Yongli

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]