MySQL query optimization: join query sort limit of join order by limit statement introduction

I don't know if anyone has ever encountered such a disgusting problem: two tables join query and limit, SQL is very efficient, but after adding order by, the statement execution time becomes extremely long and the efficiency is extremely low.

The situation is this: there are now two tables, team and people, each people belonging to one team, and people with a field team_id.
The following sentences are given:

 
create table t_team 
( 
id int primary key, 
tname varchar(100) 
); 
create table t_people 
( 
id int primary key, 
pname varchar(100), 
team_id int, 
foreign key (team_id) references t_team(id) 
); 

Now I'm going to join the two tables to find the top 10 people, sorted by tname.
Thus, an SQL statement is born: select * from

ople p left join t_team t on p team_id= t. id order by p. pname limit 10; [statement (1)]
This is the first reaction I wrote SQL, which is easy to understand and also the first reaction of most people. Then test 1 for the execution time of this statement. First prepare the data. I used the stored procedure to generate 1,000 pieces of data in the t_team table and 100,000 pieces of data in the

ople table. (stored procedures at the end of this article)
The above SQL statement was executed several times, taking about 3 seconds.

Compare the two statements 1 again:
1. Remove the order by clause: select * from

ople p join t_team t p. team_id= t. id limit10; [statement (2)]
Time: 0.00 seconds, ignored.

2. order by is still used, but the t_team table is removed from the connection: select * from

ople p order p pname limit limit 10; [statement (3)]
It takes about 0.15 seconds.
The comparison found that [statement] was extremely inefficient.
Why is it so inefficient? Both [statement] and [statement] execute very quickly, and [statement] is nothing more than a combination of the two. If [statement] is executed first to get the 10 people results sorted, then the team of each people is connected and inquired, the efficiency will not be so low. There is only one explanation: MySQL executes the join query first, then sorts.
Solution: if you want to be more efficient, modify the SQL statement so that MySQL sorts the first 10 items before joining the query.

SQL statements:
select * from (select * from

ople order by p.pname limit 10) p left t_team p.team_id = t.id limit 10; [statement (4)]
[statement] and [statement] function 1, although there are subqueries, although it looks very awkward, but the efficiency of a lot of improvement, its execution time as long as 0.16 seconds or so, compared to the previous [statement] (3 seconds) increased by 20 times.
The structure of these two tables is very simple, if you encounter a complex table structure... I encountered such a problem in the actual development, the use of [statement] in the way of more than 80 seconds, but the use of [statement] in less than 1 second.

Finally, the stored procedure of data creation is given:

 
CREATE PROCEDURE createdata() 
BEGIN 
DECLARE i INT; 
START TRANSACTION; 
SET i=0; 
WHILE i<1000 DO 
INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1)); 
SET i=i+1; 
END WHILE; 
SET i=0; 
WHILE i<100000 DO 
INSERT INTO t_people VALUES(i+1,CONCAT('people',i+1),i%1000+1); 
SET i=i+1; 
END WHILE; 
COMMIT; 
END