# Deep Paging Case Study Summary (B)

+ Cache the sorted value of the first and last row in the page and the value of the auto increment primary key key**id** column
+ On sort fields like**ename**, we need to build indexes to improve sort performance
+ (Next button logic)

```
SELECT * 
FROM emp 
WHERE ename >= 'Frank' AND (ename > 'Frank' OR id > 18) 
ORDER BY ename, id LIMIT 10;
```


![right](./student-guide/diagram/01-27-1.png)

------------------------------------------------------------

At this time, we must add the auto increment primary key id column as an additional condition to handle the problem of duplicate row between page 1 and page 2. For page 1, we can write the logic of the “Next” button like this:

```
SELECT * 
FROM emp 
WHERE ename >= 'Frank' AND (ename > 'Frank' OR id > 18) 
ORDER BY ename, id LIMIT 10;
```

For page 2, we can write the logic for the “previous page” button like this:

```
SELECT * 
FROM emp 
WHERE ename >= 'Frank' AND (ename > 'Frank' OR id > 18) 
ORDER BY ename, id LIMIT 10;
```

The conclusion is that we can avoid the performance degradation caused by LIMIT M OFFSET N (LIMIT N, M) by simply remembering the sorted column value of the first and last row and the value of the auto increment primary key id column in the page. One thing to keep in mind here is that in a sort sequence like rename, we need to build an index to improve sort performance.

```
ALTER TABLE emp ADD KEY idx_ename(ename);
```