Mysql limit Optimization Method

Mysql limit Optimization Method

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 #.

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:
Mysql> SELECT * FROM table LIMIT 5; // retrieve the first five record rows

// In other words, LIMIT n is equivalent to LIMIT 0, n.

Select * From cyclopedia Where ID> = (
Select Max (ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) Limit 100;

2.
Select * From cyclopedia Where ID> = (
Select Max (ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) Limit 100;

Is it faster to get 90000 records after 100, 1st sentences or 2nd sentences?
The first 1st records are obtained first, and the largest ID value is used as the start ID. Then, the first 90001 records can be quickly located.
The 2nd clause selects only the first record after the first record, and then takes the ID value as the starting marker to locate the next 90000 records.
1st statement execution result. 100 rows in set (0.23) sec
2nd statement execution result. 100 rows in set (0.19) sec

Obviously, 2nd sentences won. it seems that limit does not seem as much as I previously imagined to do a full table scan and return limit offset + length records, so it seems that limit is much better than the Top performance of the MS-SQL.

In fact, 2nd sentences can be simplified

Select * From cyclopedia Where ID> = (
Select ID From cyclopedia limit 90000,1
) Limit 100;

Using the IDs of 90,000th records directly does not require the Max operation. In this way, the theoretical efficiency is higher, but the results are almost invisible in actual use, because the ID returned by the positioning itself is a record, Max can get the result almost without operation, but this write is clearer, saving the time to draw a snake.

However, since MySQL has a limit that can directly control the location where records are retrieved, why not simply use Select * From cyclopedia limit, 1? Isn't it more concise?