Most Efficient Way to do Pagination/Rough Estimate

问题:

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

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)).

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.

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".

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.

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.

回答1:

Another interesting idea I found:

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.

回答2:

It is an old topic that was beaten to death. Many times. Count is the fastest way to get number of rows in a typical table. But if you never delete anything from it (which is a weird assumption, but will work in some cases.), then you could simply get ID of the last row (which may be faster, but not necessarily). This would also fit your estimations need, as most likely won't be correct.

But then again, if you are using for example myisam, then nothing beats count (which is true for most cases).