mysql limit collapse, which result in data interaction

Question

I use limit offset, num to fetch data by page. But the data between pages interact, which can be seen from the primay key course_id.

select version();
+---------------------+
| version()           |
+---------------------+
| 10.3.13-MariaDB-log |
+---------------------+

show index from sc_base_course;
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sc_base_course |          0 | PRIMARY   |            1 | course_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| sc_base_course |          1 | agency_id |            1 | agency_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| sc_base_course |          1 | agency_id |            2 | course_name | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Get the first page 10 rows;

select course_id,grade_id from sc_base_course where  agency_id = 10000  limit 0,10;
+-----------+----------+
| course_id | grade_id |
+-----------+----------+
|        13 |        1 |
|         6 |        3 |
|        12 |        4 |
|         8 |        2 |
|         7 |        2 |
|         9 |        4 |
|        16 |        1 |
|         1 |        2 |
|        17 |        1 |
|        14 |        5 |
+-----------+----------+

Get the second page 7 rows

select course_id,grade_id from sc_base_course where  agency_id = 10000  limit 10,10;
+-----------+----------+
| course_id | grade_id |
+-----------+----------+
|        11 |        4 |
|        12 |        4 |
|        13 |        1 |
|        14 |        5 |
|        15 |        1 |
|        16 |        1 |
|        17 |        1 |
+-----------+----------+

https://use-the-index-luke.com/sql/partial-results/fetch-next-page — jarlh, Sep 23 '20 at 09:28
Using `OFFSET` for pagination is error prone and does not scale well: http://mysql.rjweb.org/doc.php/pagination — Rick James, Sep 23 '20 at 22:37
@RickJames +1 for the deeply well analysis on the pagination. Thank you. — LF00, Sep 24 '20 at 01:22
@KrisRoofe - Thanks. How would you compare my writeup to the one from 'use-the-index-luke'? — Rick James, Sep 24 '20 at 18:30

score 2 · Accepted Answer · answered Sep 23 '20 at 09:28

Use order by!

SQL tables represent unordered sets of rows. Without an order by clause, the database is free to return the rows in whichever order it likes, and the results may not be consistent over consecutive executions of the same query (hence, pagination is not stable).

select course_id, grade_id 
from sc_base_course 
where  agency_id = 10000  
order by course_id, grade_id
limit 10,10;

Note that not only you need an order by clause, but also this clause must be deterministic. That is, the column (or set of columns) in the clause must uniquely identify each record - otherwise, it is, again, undefined in which order ties will be fetched.

What would benefit from `INDEX(agency_id, course_id, grade_id)` -- in that order. — Rick James, Sep 23 '20 at 22:36

mysql limit collapse, which result in data interaction

1 Answers1

Linked