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 onp. 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.
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
Reprinted from: http://blog.csdn.net/xiao__gui/article/details/8616224