MySQL Query Optimization: connection query sorting limit (join, orderby, limit statement) bitsCN.com
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 for query and limit, and the SQL efficiency is very high,
However, after the order by clause is added, the statement execution time becomes longer and the efficiency is greatly reduced.
Www.bitsCN.com
There are two tables, team table and people Table. each people belongs to one
Team, people has a field team_id.
The following table creation statement is provided:
[SQL]
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 a stored procedure to generate 1000 pieces of data in the t_team table. in the t_people table
Generate 100000 data records. (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, then connect to query
Team, the efficiency is not 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]
The functions of [Statement 4] are the same as those of [statement 1]. although subqueries look awkward, the efficiency has improved a lot,
The execution time is about 0.16 seconds, which is 20 times higher than the previous [statement 1.
The structure of these two tables is very simple. if you encounter complicated table structures... I encountered this in actual development.
The problem is that it takes more than 80 seconds to use [statement ①], but it takes less than 1 second to use [statement ④.
Finally, we provide the data creation stored procedure:
[SQL]
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
SOURCE http://blog.csdn.net/xiao__gui/article/details/8616224
BitsCN.com