I was wondering if there was a way to get the number of results from a MySQL query, and at the same time limit the results.
The way pagination works (as I understand it), first I do something like
query = SELECT COUNT(*) FROM `table` WHERE `some_condition`
query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10
No, that's how many applications that want to paginate have to do it. It's reliable and bullet-proof, albeit it makes the query twice. But you can cache the count for a few seconds and that will help a lot.
The other way is to use
SQL_CALC_FOUND_ROWS clause and then call
SELECT FOUND_ROWS(). apart from the fact you have to put the
FOUND_ROWS() call afterwards, there is a problem with this: There is a bug in MySQL that this tickles that affects
ORDER BY queries making it much slower on large tables than the naive approach of two queries.