Mysqllimit query optimization analysis

MYSQL optimization is very important. The other most commonly used and most needed optimization is limit. Mysql limit greatly facilitates paging. However, when the data volume is large, the limit performance drops sharply. the Limit syntax:

The code is as follows:

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

The LIMIT clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. The parameter must be an integer constant.
If two parameters are specified, 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 rather than 1 ).
To be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # OFFSET #.

Eg:

The code is as follows:

Mysql> SELECT * FROM table LIMIT 5, 10; // retrieves records FROM 6 to 15 rows.

// To retrieve all record rows from an offset to the end of the record set, you can specify the second parameter-1
Mysql> SELECT * FROM table LIMIT 95,-1; // retrieves 96-last Records.

// If only one parameter is specified, it indicates the maximum number of record rows returned. In other words, LIMIT n is equivalent to LIMIT 0, n
Mysql> SELECT * FROM table LIMIT 5; // retrieve the first five record rows

MySQL limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.
10 data records are also obtained. the following two statements are not an order of magnitude.

The code is as follows:

Select * from table limit, 10
Select * from table limit 0, 10

Instead of using limit directly, we first get the offset id and then use limit size to get data. Based on his data, it is much better to use limit directly.

Here I use data in two cases for testing.

1. when offset is small:

The code is as follows:

Select * from table limit 10, 10
// Run multiple times, with the duration between 0.0004 and 0.0005
Select * From table Where vid> = (Select vid From table Order By vid limit 10, 1) limit 10
// Run multiple times, with the duration between 0.0005 and 0.0006, mainly 0.0006

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

2. when the offset value is large:

The code is as follows:

Select * from table limit, 10
// Run multiple times, with a time of around 0.0187

Select * From table Where vid> = (Select vid From table Order By vid limit, 1) limit 10
// Run multiple times, with a time of around 0.0061, only 1/3 of the former. It can be predicted that the larger the offset, the higher the latter.