Optimize MySQL limit offset

Limit and offset are usually used together with order. Indexes are helpful for sorting and comparison,

If no index is available, a large number of files are sorted. Therefore, you must add an index to the order by column.

If:

Select * from mytable order by sp_id limit 100000,10

When similar SQL statements appear, the operation costs a lot and will scan 100010 rows of data.

This can be changed

Select * from mytable

Inner join (

Select pid from mytable order by sp_id limit 100000,10

) As lim using (pid );

Note: sp_id creates a BTREE index.

The server can check as little data as possible on the index. Once the required rows are obtained, they are connected to the complete table and the remaining columns are obtained.

The SQL statement execution time before modification is 2.10 seconds, and the modified SQL statement execution time is 1.75 seconds. Saves 0.35 seconds.