MySQL query optimization: a brief introduction to join query sorting

The situation is this: there are now two tables, team and people, and each people belongs to one team, and people has 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 join t_team t onp. 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 stored procedures to generate 1000 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, in about three seconds.

Compare the two statements 1 again:

1. order by clause: select * from

ople left join t_t on on on id. team_id= t. id limit10; [statement (2)]

Time: 0.00 seconds, ignored.

2. order by again, but remove the t_team table from the connection: select * from

ople p order by p. pname 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 sorted results, then the team of each people is connected and queried, 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 improve efficiency, modify the SQL statement so that MySQL sorts the first 10 items before joining the query.

SQL statements:

select * from (select * from

ople order order order limit 10 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] 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

Reprinted from: http: / / blog csdn. net/xiao__gui/article/details / 8616224