Page 1 of 1

SELECT columns with LIMIT and COUNT in the same query?

Posted: Wed Jul 27, 2005 5:17 pm
by Ambush Commander
Suppose I'm using LIMIT to constrain the values MySQL returns. Is there any way for me to, in the same query, count all the rows without any constraints (for pagination), or do I have to do another query with COUNT(*)? And how long does it generally take for MySQL to COUNT(*)?

Posted: Wed Jul 27, 2005 5:27 pm
by timvw
Well, LIMIT is only applied after the SELECT... So that wouldn't be the problem..

But the problem is that you can't use aggregate functions (count, max, min) together with a regular select...


A compromise would be to perform a SELECT COUNT(*) $where the first time you start pagination.. And store it in a session (where you also store the $rows_per_page etc..)

Don't forget to update the count when the $where changes ;)

Posted: Wed Jul 27, 2005 5:31 pm
by Ambush Commander
Ah, that works. Never thought of that.