MySQL Limit query optimization analysis _php skills

Limit syntax:

Copy Code code as follows:

SELECT * FROM table LIMIT [offset,] rows | Rows Offset Offset

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

eg

Copy Code code as follows:

Mysql> SELECT * FROM table LIMIT 5, 10; Retrieve Record Row 6-15

To retrieve all row rows from an offset to the end of the recordset, you can specify a second parameter of-1
Mysql> SELECT * from table LIMIT 95,-1; Retrieving record rows 96-last

If only one argument is given, it represents 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 5 rows of records

The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.
The same is to take 10 data, the following two sentences is not a quantitative level.

Copy Code code as follows:

SELECT * FROM table limit 10000,10
SELECT * FROM table limit 0,10

Instead of using limit directly, we first get the ID of offset and then use limit size directly to get the data. According to his data, it is significantly better than using limit directly.

Here I use the data in two different situations to test.

1, offset relatively small time:

Copy Code code as follows:

SELECT * FROM table limit 10,10
Run multiple times, keeping the time between 0.0004-0.0005
SELECT * FROM table Where vid >= (select vid to Table order by vid limit 10,1) limit 10
Run multiple times, keeping the time between 0.0005-0.0006, mainly 0.0006

Conclusion: The direct use of limit is more excellent when offset is smaller. This is obviously the reason for the subquery.

2. When the offset is big:

Copy Code code as follows:

SELECT * FROM table limit 10000,10
Run more than once and keep the time around 0.0187

SELECT * FROM table Where vid >= (select vid to Table order by vid limit 10000,1) limit 10
Run several times, the time remained at about 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.