最有效的分页方法/行总数的粗略估计 [英] Most Efficient Way to do Pagination/Rough Estimate of Total Number of Rows

问题描述

我有一个非常普遍的问题，但似乎找不到很好的答案.

I have a very common problem, but cannot seem to find a good answer for it.

我需要从表中获取页面的行值，以及足够的信息以对这些数据进行分页.因此，总的来说，我需要对行的总数进行非常粗略的估算(总的来说，我需要知道的是ceil(count()/50)).

I need to get a page's worth of rows from a table, as well as enough info to paginate this data. So in general I need a very rough estimate of the total number of rows (in general All I need to know is ceil(count()/50)).

所以count()真的是太过分了.而且我已经在运行SELECT * FROM table LIMIT 0, 50，因此，如果可以将它附加到此命令中，那就更好了.

So count() is really overkill. And I already have a SELECT * FROM table LIMIT 0, 50 running, so if it can be appended to this command all the better.

我听说过SQL_CALC_FOUND_ROWS.但是我也听说，它不仅比自己做count更有效. 不幸的是，使用SQL_CALC_FOUND_ROWS会带来令人讨厌的后果，那就是取消可能发生的任何LIMIT优化."

I have heard about SQL_CALC_FOUND_ROWS. But I also heard that it is not particularly more efficient than just doing the count yourself. "Unfortunately, using SQL_CALC_FOUND_ROWS has the nasty consequence of blowing away any LIMIT optimization that might happen".

因此，总的来说，我认为使用MySQL的行估算值是可行的方法.但是我不知道该怎么做.或这个估计可能有多差.

So, all in all, I kindof think using MySQL's row estimate is the way to go. But I do not know how to do that. Or how off this estimate might be.

注1:在我的情况下，我正在使用的大多数表每天都会更新几次，而不是每次都更新. 注意2:我正在将PDO与php一起使用.

Note1: In my situation most of the tables I am working with are just updated a few times a day, not all the time. Note2: I am using PDO with php.

推荐答案

我发现了另一个有趣的主意:

Another interesting idea I found:

更好的设计是将寻呼机转换为下一个"链接.假设每页有20个结果，则查询应使用LIMIT(限制)为21行并仅显示20.如果结果中存在第21行，则存在下一页，您可以呈现下一个"链接.

A better design is to convert the pager to a "next" link. Assuming there are 20 results per page, the query should then use a LIMIT of 21 rows and display only 20. If the 21st row exists in the results, there’s a next page, and you can render the "next" link.

如果您不需要表的总数，那确实是最快的解决方案.

If you don't need the total count of the table it's indeed the fastests solution.

这篇关于最有效的分页方法/行总数的粗略估计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！