A simple paging optimization method of MySQL in the case of big data

In general, the application needs to page the data in the table. If the data volume is large, it will often cause performance problems:

root@sns 07:16:25>select count(*) from reply_0004 where thread_id = 5616385 and deleted = 0;
+ -- - -+
| count(*) |
+ -- - -+
| 1236795 |
+ -- - -+
1 row in set (0.44 sec)
root@sns 07:16:30>select id
from reply_0004 where thread_id = 5616385 and deleted = 0
order by id asc limit 1236785, 10 ;
+ -- � +
| id    |
+ -- � +
| 162436798 |
| 162438180 |
| 162440102 |
| 162442044 |
| 162479222 |
| 162479598 |
| 162514705 |
| 162832588 |
| 162863394 |
| 162899685 |
+ -- � +
10 rows in set (1.32 sec)

Index: threa_id+deleted+id (gmt_Create)
10 rows in set (1.32 sec)
These two sql are used for the paging sql query on the last page of the query. Since only small data, such as 10 pieces of data, need to be searched for a single page turning, but a large amount of data needs to be scanned backwards. In other words, the more the page turning query is in the future, the more data will be scanned and the slower the query speed will be.
Since the size of the query is fixed, it is best if the query speed is not affected by the number of pages turned, or if the impact is minimal (the speed of the last few pages of the query and the speed of the first few pages).
When turning a page, it is often necessary to sort one of the fields (this field is in the index) in ascending order. Can the ordering of indexes be used to solve the above problems? The answer is yes. For example, if 10,000 pieces of data need to be paginated, then the first 5000 pieces should be sorted by asc, and the last 5000 pieces should be sorted by desc, and corresponding adjustments should be made in the parameters of limit startnum and pagesize.
But this undoubtedly brings complexity to the application. This sql is an sql for forum reply posts. When users are reading a post, they usually view the first and last pages.

root@snsgroup 07:16:49>select * from (select id

->      from group_thread_reply_0004 where thread_id = 5616385 and deleted = 0

->    order by id desc limit 0, 10)t order by t.id asc;

+ -- � +

| id    |

+ -- � +

| 162436798 |

| 162438180 |

| 162440102 |

| 162442044 |

| 162479222 |

| 162479598 |

| 162514705 |

| 162832588 |

| 162863394 |

| 162899685 |

+ -- � +

10 rows in set (0.87 sec)

You can see a performance improvement of more than 50%.