View Full Version : count, limit

06-12-2011, 06:49 PM
I have a sql that does:

Select ...
limit 10,20

I also need to know how many record are there in total, without limit.

Excluding running another sql, is there a better way to get that ?

Having something inside stored proc, but alltogether turned out to be super slow,
as oposed to running only select ... limit.

EDIT: figured out that sending sql string into stored proc
and 'executing' it via prepare statement
tremenduously slows things down !!!

So, the performance increases tremenduously even if I call db twice (for 2 sql-s) instead.
Didn't test stored proc without prepare doh.

06-12-2011, 11:43 PM
$num_rows = mysql_num_rows($results);
echo $num_rows;

Played with this using one query, but it think two queries is the fastest route.

06-13-2011, 01:52 AM
I have been educated of func SQL_CALC_FOUND_ROWS (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows)

Which looks great (and should work like LAST_INSERT_ID()), if it would work.
Does not, SELECT FOUND_ROWS() always returns 1 for me.

At the end the only way that I got it working was like:

Select SQL_CALC_FOUND_ROWS some_field
FROM table
LIMIT 0,18



Dropped the idea.