# Deep Paging Case Study Background

+ Table definition:
+ paging SQL query:

```
CREATE TABLE t1 (
 id int(11) NOT NULL AUTO_INCREMENT,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id),
 KEY k_1 (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

SELECT id, k FROM t1 ORDER BY id LIMIT 0,10;
...
SELECT id, k FROM t1 ORDER BY id LIMIT 10000,10;
```
------------------------------------------------------------
backgrounds

When the front-end page needs to display a large amount of data, in order to improve the user experience and prevent the front-end memory overhead from loading too much data at once, development students generally perform paging query and displays. At this time, the LIMIT syntax of the database is used. The function is implementation, but it doesn't mean there must be no problem, because LIMIT is used in the wrong way, which can lead to deep paging performance issues, affecting the user experience and stability of our database.

Specific case

We have a t1 table with the following table schema:

```
CREATE TABLE t1 (
 id int(11) NOT NULL AUTO_INCREMENT,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id),
 KEY k_1 (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```

At first, there were only tens of thousands of data in the table, test paging.

```
SELECT id,k FROM t1 ORDER BY id LIMIT 0,10;
...
SELECT id,k FROM t1 ORDER BY id LIMIT 10000,10;
```