Mysql – How to COUNT number of rows with LIMIT

countMySQLmysql-5.5select

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?

Currently, I use two separate queries to do so as

SELECT COUNT(*) FROM col WHERE CLAUSE
SELECT * FROM col WHERE CLAUSE LIMIT X

Is there a way to do this in one query?

EDIT: The output should be the col cells and the number of rows. In fact, after selecting the col cells, it should walk over the table to count only.

I know that it is not possible to merge these two queries, as the first return 1 row, but the second X rows. I am curious if there is a function in mysql to return the number of rows in the presence of LIMIT.

Best Answer

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

Related Solutions

Mysql – Limit WHERE to MAX() & COUNT()

Here is your original query from the question

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM TAB_EVENT e 
LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id 
WHERE ( m.`read` IS NULL OR m.`read` = 0) 
GROUP BY e.id 
ORDER BY m.datetime DESC, e.id ASC 
LIMIT 10;

Maybe try refactoring the query in such a way that in executes in this sequence

only collect necessary columns from TAB_MESSAGE
apply LIMIT 10 against the collected rows from TAB_MESSAGE
run the JOIN
apply the MAX() and COUNT() last

Here is what I am proposing

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
LEFT JOIN TAB_EVENT e 
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

Give it a Try !!!

UPDATE 2012-02-21 17:06 EDT

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

@Sebastian, I put the query back in the original join order. Please try this as well !!!

UPDATE 2012-02-21 17:11 EDT

Make sure the datetime field is indexed

ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);

Mysql – How to select specific rows if a column exists or all rows if a column doesn’t

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT 
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true