0

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.

Jonathon
  • 2,571
  • 5
  • 28
  • 49
  • 2
    Why are you so against doing `COUNT(*)`? – Mike May 02 '13 at 21:04
  • 2
    I went through the question thrice and all I understood was that you don't want to use `COUNT` but still want to get the count.... – hjpotter92 May 02 '13 at 21:08
  • Because it needs to iterate through the entire table. And I am sure it has been optimised immensely, but SQL does not use `count` or anything else when it needs to know an estimate of the total number of rows. It uses an estimate, because `count` is not insignificant. – Jonathon May 02 '13 at 21:09
  • If you really want to optimize your counts, have a look to [this answer](http://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql). – Alain Tiemblo May 02 '13 at 21:15

2 Answers2

6

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.

bitWorking
  • 12,485
  • 1
  • 32
  • 38
0

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

Tymoteusz Paul
  • 2,732
  • 17
  • 20
  • I have heard that `count` actually can get quite bad and millions of rows leads to seconds of execution time. Which is completely not even close to acceptable for pagination purposes. – Jonathon May 02 '13 at 21:30
  • Quite possible, depends on hardware and table structure. Key point is that it's the most efficient way out there. There is nothing better. No magical function that is kept secret from normal users. – Tymoteusz Paul May 02 '13 at 21:35
  • I disagree, and it really does not matter. You simply cannot have a query that takes several seconds on a public facing website. It is not a matter of, technically it is slightly annoying that it is not more efficient, it makes it impossible to use in many situations. – Jonathon May 02 '13 at 22:25
  • Sure you can, but you have to be smart about it. Multiple apps I have developed have queries that run for couple minutes (aggregating a lot of data across multiple data sources and so on) and yet for user it is transparent. It's matter of proper caching of data and refreshing it "when needed. So you could store the bit called "posts count" in memcache and access it as needed, and then run a cron task every minute to update said bit. Problem solved. – Tymoteusz Paul May 02 '13 at 22:34