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

MySQL query optimization: Connection query sorting limit (join, order by, limit statements) does not 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.2cto.com: There are two tables, team table and people table, each of which 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 varchar (100 ), team_id int, foreign key (team_id) references t_team (id); Under www.2cto.com, 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 first wrote. It is easy to understand and is also the first reaction 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. (The stored procedure is at the end of this article) It takes about 3 seconds to execute the preceding SQL statement several times. 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 ②] 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 ③] 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 ④] [Statement ④] is the same as [Statement ①]. Although subqueries look awkward, their efficiency is 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, the data creation stored PROCEDURE is provided: [SQL] 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 ('others', I + 1), I % 1000 + 1 ); SET I = I + 1; END WHILE; COMMIT; END source http://blog.csdn.net/xiao__gui/article/details/8616224