I wonder if anyone has ever had such a disgusting problem: Two tables connection query and limit,sql efficiency is very high, but add order by after, the execution time of the statement becomes huge, the efficiency is very low.
The situation is the case: there are now two tables, a team table and a people table, each people belonging to a team,people with a field team_id.
The following is a table statement:
Copy Code code as follows:
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)
);
Next I want to connect two tables query out the first 10 people, sorted by Tname.
As a result, an SQL statement was born: SELECT * from T_people P-left join T_team T in p.team_id=t.id Order by p.pname limit 10; [Statement ①]
This is my first reaction to write SQL, easy to understand, but also most people's first reaction. Then test the execution time of the statement. The first thing to do is prepare the data. I used a stored procedure to generate 1000 data in the T_team table and 100,000 data in the T_people table. (Stored procedure at the end of this article)
Execute the above SQL statement several times, taking about 3 seconds.
Another two statements to compare:
1. Remove the ORDER BY clause: SELECT * from T_people p LEFT join T_team T on P.team_id=t.id limit10; [Statement ②]
Takes 0.00 seconds, negligible.
2. Or use order BY, but remove the connection t_team table: SELECT * from T_people P ORDER by p.pname limit 10; [Statement ③]
It takes about 0.15 seconds.
The comparison found that [statement ①] is inefficient.
Why is it so inefficient? [Statement ②] and [statement ③] Execute quickly, [statement ①] is just a combination of the two. If the first execution [statement ③] obtains the sorted 10 people result, the connection inquires out each people's team, the efficiency is not so low. Then there is only one explanation: MySQL executes the connection query before sorting.
Solution: If you want to improve efficiency, you need to modify the SQL statements, let MySQL first sort take the top 10 and then connect the query.
SQL statement:
SELECT * FROM (SELECT * from T_people P-p.pname limit) p left join T_team t on p.team_id=t.id limit 10; [Statement ④]
[Statement ④] and [statement ①] functions, although there are subqueries, although seemingly awkward, but more efficient, its execution time of only about 0.16 seconds, than the previous [statement ①] (3 seconds) increased 20 times times.
The structure of these two tables is simple, if you encounter a complex table structure ... I encountered this problem in actual development, using the [statement ①] method takes more than 80 seconds, but use [statement ④] only within 1 seconds.
Finally, the stored procedure for making data is given:
Copy Code code as follows:
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
