MySQL Query Optimization: connection query sorting limit (join, orderby, limit statement) _ MySQL

Source: Internet
Author: User
Tags mysql query optimization
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.