mysql limit query optimization analysis_PHP tutorial-PHP Tutorial-php.cn

mysql limit query optimization analysis_PHP tutorial

WBOY

Release： 2016-07-21 15:49:11

Original

891 people have browsed it

Limit syntax:

Copy code The code is as follows:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force a SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The parameter must be an integer constant.
If two parameters are given, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the initial record row is 0 (not 1).
For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.

eg:

Copy code The code is as follows:

mysql> SELECT * FROM table LIMIT 5,10; //Retrieve record row 6- 15

//In order to retrieve all record rows from a certain offset to the end of the record set, you can specify the second parameter as -1
mysql> SELECT * FROM table LIMIT 95,-1; //Retrieval Record line 96-last

//If only one parameter is given, it means returning the maximum number of record rows. In other words, LIMIT n is equivalent to LIMIT 0,n
mysql> SELECT * FROM table LIMIT 5; //Before retrieval 5 record lines

MySQL’s limit brings great convenience to paging, but when the amount of data is large, the performance of limit drops sharply.
We also take 10 pieces of data, but the following two sentences are not of the same order of magnitude.

Copy code The code is as follows:

select * from table limit 10000,10
select * from table limit 0, 10

In this article, instead of using limit directly, we first obtain the ID of the offset and then directly use the limit size to obtain the data. According to his data, it is obviously better than using limit directly.

Here I specifically use data to test in two situations.

1. When the offset is relatively small:

Copy code The code is as follows:

select * from table limit 10,10 
//Multiple runs, time Keep between 0.0004-0.0005
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10 
//Run multiple times, the time should keep between 0.0005-0.0006 , mainly 0.0006

Conclusion: When the offset offset is small, it is better to use limit directly. This is obviously the reason for the subquery.

2. When the offset is large:

Copy code The code is as follows:

select * from table limit 10000,10
//Multiple runs, time Stay around 0.0187

Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10
//Run multiple times, the time remains at around 0.0061, only 1/3 of the former. It can be expected that the larger the offset, the better the latter.