最有效的分页方法/行总数的粗略估计 [英] 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屋!