Mysql之LIMIT-OFFSET查询时间过长处理办法

This article explains why LIMIT-OFFSET queries are slow on large tables. In the example, querying a single row with OFFSET 800000 takes over 2 seconds. The reason: MySQL must first find 800001 index leaf nodes, then query 800001 rows from the clustered index using those primary keys, and finally discard the first 800000 rows. This wastes significant disk I/O. Two optimization approaches are provided: 1) If ordering by primary key, use WHERE id > last_id to directly query only the needed rows, avoiding unnecessary I/O. 2) For complex conditions and sorting, use INNER JOIN to first retrieve primary keys, then query the main table using those IDs.

MySQL [yuetong1]> SELECT * FROM pa_order LIMIT 1 OFFSET 800000;
//查询类容不展示了
1 row in set (2.04 sec)

MySQL [yuetong1]> SELECT * FROM pa_order WHERE id > 800000 LIMIT 1;
//查询类容不展示了
1 row in set (0.01 sec)

MySQL [yuetong1]> select * from pa_order as a inner join (select id from pa_order LIMIT 1 OFFSET 800000) as b on a.id=b.id;
//查询类容不展示了
1 row in set (0.12 sec)