SELECT columns with LIMIT and COUNT in the same query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

SELECT columns with LIMIT and COUNT in the same query?

Post 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(*)?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Ah, that works. Never thought of that.
Post Reply