Mysql – COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

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);

I agree with @RemusRusanu (+1 for his answer)

SELECT COUNT(*) FROM mydb.mytable in InnoDB behaves like a transactional storage engine should. Compare it to MyISAM.

MyISAM

If mydb.mytable is a MyISAM table, launching SELECT COUNT(*) FROM mydb.mytable; is just like running SELECT table_rows FROM information_schema.table WHERE table_schema = 'mydb' AND table_name = 'mytable';. This triggers a quick lookup of the row count in the header of the MyISAM table.

InnoDB

If mydb.mytable is a InnoDB table, you get hodge-podge of things going on. You have MVCC going on, governing the following:

• ib_logfile0/ib_logfile1 (Redo Logs)
• ibdata1
  • Undo Logs
  • Rollbacks
  • Data Dictionary Changes
• Buffer Pool Management
• Transaction Isolation (4 types)
  • Repeatable Reads
  • Read Committed
  • Read Uncommitted
  • Serializable

Asking InnoDB for a table count requires navigation through these ominous things. In fact, one never really knows if SELECT COUNT(*) from mydb.mytable counts repeatable reads only or includes reads that have been committed and those that are uncommitted.

You could try to stabilize things a little by enabling innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_meta_data

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Disabling it may or may not give you a more stable count in terms of setting up EXPLAIN plans. It may affect performance of SELECT COUNT(*) from mydb.mytable in either a good way, bad way, or not at all. Give it a Try and See !!!