There is a well-known problem about large tables and LIMIT design brakes on the last pages of pagination. For example, if we have a table with records of more than 500,000, these brakes are already beginning to be felt. Is there a ready-made solution in the form of a paginator class, for example, to get rid of this problem? If I switch to postgresql there is the same problem?

  • Here is a wonderful article on the topic of pagination, unfortunately not translated into Russian - Dmitry V.
  • And so, without a difference, that mysql, that postgres, in order to retreat, N rows must sort the table and count. Clipping from postgresql documentation: The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient. The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient. - Dmitry V.
  • 2
    1) you really rarely need to show all the search pages, look at the same Google - after a few dozen pages he will say that nothing has been found; 2) you can optimize a little pagination: remember not so much the offset as the last found values. For example, sorting is by id, instead of displaying page 100000, you look for where id > XXX limit 50 , where XXX is the last id from the last sample. Then the sample will be much faster, because You do not need to look for 100,000 entries before the current one But it will work fine only when searching by index - BOPOH
  • 2
    3) you can show the approximate number of pages, but not the exact one: get through the explain query execution plan and calculate how many pages there will be. Jambs will be noticeable when in reality there will be few records, and explain will show a lot. Although the same search engines do the same, I watched this in Yandex (showed 10 pages, but gave out only 3) 4) and you can always say: damn, while you were flipping through the real picture changed, therefore such results turned out. Those. if the user has moved to the 100 page, then it is not necessary to show exactly the 100 page, you can show "about 100")) - BOPOH
  • one
    And where does php. This is an understandable DB problem and can only be solved at the DB level by modifying queries, and I’m afraid I’ll have to do this for a specific case, depending on what data it is. From the php side, you can of course assume caching of some subsequent data coming after the requested piece. but it is still not clear what is more expensive, choose how it is now or cache what 95% is not needed - Mike

1 answer 1

Reply from the comments from RAVEN

1) you really rarely need to show all the search pages, look at the same Google - after a few dozen pages he will say that nothing has been found;

2) you can optimize a little pagination: remember not so much the offset as the last found values. For example, sorting is by id , instead of displaying page 100000, you look for where id > XXX limit 50 , where XXX is the last id from the last sample. Then the sample will be much faster, because You do not need to look for 100,000 entries before the current one But it will work fine, only when searching by index.

3) you can show the approximate number of pages, but not the exact one: get through the explain query execution plan and calculate how many pages there will be. Jambs will be noticeable when in reality there will be few records, and explain will show a lot. Although the same search engines do the same, I watched this in Yandex (showed 10 pages, but gave out only 3) 4) and you can always say: damn, while you were flipping through the real picture changed, therefore such results turned out. Those. if the user has moved to 100 page, then it is not necessary to show exactly 100 page, you can show "about 100"))