MySQL query optimization: Connection query sorting limit (join, order by, limit statements)

I don't know if anyone has encountered such a disgusting problem: two tables are connected to the query and limit. SQL is very efficient. However, after order by is added, the statement execution time has become very long, extremely low efficiency.

This is the case: there are two tables, team table and people table. Each people belongs to a team and there is a field team_id in people.
The following table creation statement is provided:Copy codeThe Code is as follows: create table t_team
(
Id int primary key,
Tname varchar (100)
);
Create table t_people
(
Id int primary key,
Pname varchiar (100 ),
Team_id int,
Foreign key (team_id) references t_team (id)
);

Next I want to connect two tables to query the first 10 people, sorted by tname.
Therefore, an SQL statement was born: select * from t_people p left join t_team t on p. team_id = t. id order by p. pname limit 10; [Statement ①]
This is the SQL statement I wrote first, which is easy to understand and the first response of most people. Then we can test the execution time of this statement. Prepare data first. I used the stored procedure to generate 1000 pieces of data in the t_team table and 100000 pieces of data in the t_people table. (Stored procedure is at the end of this article)
Execute the preceding SQL statement several times, which takes about 3 seconds.

Then compare the two statements:
1. Remove the order by clause: select * from t_people p left join t_team t on p. team_id = t. id limit10; [Statement ②]
It takes 0.00 seconds.

2. Use order by, but remove the join t_team table: select * from t_people p order by p. pname limit 10; [Statement ③]
It takes about 0.15 seconds.
Comparison shows that [Statement 1] is very inefficient.
Why is efficiency so low. [Statement ②] and [Statement ③] are executed very quickly. [Statement ①] is just a combination of the two. If you execute [Statement ③] to obtain 10 sorted people results and then connect to the teams of each people, the efficiency will not be so low. There is only one explanation: MySQL first executes the connection query and then sorts it.
Solution: to improve efficiency, modify the SQL statement so that MySQL can sort the first 10 results and connect to the query.

SQL statement:
Select * from (select * from t_people p order by p. pname limit 10) p left join t_team t on p. team_id = t. id limit 10; [Statement 4]
[Statement ④] is the same as [Statement ①]. Although subqueries look awkward, the efficiency is improved a lot. It takes about 0.16 seconds to execute them, it is 20 times higher than the previous [Statement 1] (3 seconds.
The structure of these two tables is very simple. If you encounter complicated table structures... In actual development, I encountered this problem. Using [Statement ①] takes more than 80 seconds, but using [Statement ④] takes less than 1 second.

Finally, we provide the data creation stored procedure:Copy codeThe Code is as follows: create procedure createdata ()
BEGIN
DECLARE I INT;
Start transaction;
SET I = 0;
WHILE I & lt; 1000 DO
Insert into t_team VALUES (I + 1, CONCAT ('team', I + 1 ));
SET I = I + 1;
End while;
SET I = 0;
WHILE I & lt; 100000 DO
Insert into t_people VALUES (I + 1, CONCAT ('people', I + 1), I % 1000 + 1 );
SET I = I + 1;
End while;
COMMIT;
END