Using SQL_CALC_FOUND_ROWS

[elektronaut]

I found this very nice feature SQL_CALC_FOUND_ROWS to use if you want to get an idea of how many records were found, even though you’re using limit. I was delighted to find this. Before I was using a count query which was always very annoying to build and it went against all my instincts;)

Is there a catch? Any reason why not to use this feature. It does slow down the query some, but i guess that’s to be expected.

Thanks for any comments on this.

[Peter]

Yes, There is the catch )

SQL_CALC_FOUND_ROWS may be only marginally faster than select count() + select with limit, sometimes it could even be slower if EXPLAIN for count() is different than one for data retrieval.

If SQL_CALC_FOUND_ROWS is used what happens is - MySQL Simply stops sending rows back to the client after LIMIT clause is satisfied - they still however need to be internally retrieved to be counted.

So in many cases it makes sense to use summary table with precomputed counters instead of count(*) or SQL_CALC_FOUND_rows

[elektronaut]

uhh…this is not a very nice discovery;)

First: this means that my indizes aren’t used for sorting (see other question)

Second: Previously i always used a count query which was the same as the original without any sorting…which means, it runs a lot faster!

Pity…