Usually, the application needs to page the data in the table , If the amount of data is large , Performance issues often arise :
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) Indexes :threa_id+deleted+id(gmt_Create) 10 rows in set (1.32
sec)
These two sql Is to query the page turning of the last page sql For inquiry . Because only a small amount of data needs to be queried at a time , as 10 strip , But it needs to scan back a lot of data , That is to say, the later you turn the page to query , More data to scan , The speed of query is getting slower and slower .
Because the data size of the query is fixed , If the query speed is not affected by the number of pages turned , Or the lowest impact , So that's the best result ( The speed of the last few pages of the query is the same as that of the first few pages ).
When turning the page , You often need to sort one of the fields ( This field is in the index ), Ascending sort . Can we use the order of index to solve the above problems , The answer is yes . For example 10000 Data need to be paged , So before 5000 Strip work asc sort , after 5000 strip desc sort , stay limit
startnum,pagesize Adjust the parameters accordingly .
But it certainly complicates the application , This one sql It's for forum reply sql, When users are reading posts , It's usually looking at the first and last pages , Then, when turning the page, the last few pages of turning query adopt desc How to turn pages , In this way, the performance can be improved :
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 the performance improvement 50% above .

Technology
©2019-2020 Toolsou All rights reserved,
It's unexpected Python Cherry tree (turtle The gorgeous style of Library )html Writing about cherry trees , Writing about cherry trees java Four functional interfaces ( a key , simple ) Browser kernel ( understand )06【 Interpretation according to the frame 】 Data range filtering -- awesome HashMap Explain in detail os Simple use of module computer network --- Basic concepts of computer network ( agreement , system ) Some East 14 Pay change 16 salary , Sincerity or routine ?