Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my current mySQL query:

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;

I get: All TAB_EVENT rows which have no TAB_MESSAGE messages at all or have at least one unread one (read = BOOLEAN DEFAULT 0).

I need: All TAB_EVENT rows no matter of their messages AND the datetime of the newest unread and the count of unread messages for each TAB_EVENT.

There may be thousands of TAB_EVENT rows and many more TAB_MESSAGE rows and I'd like to avoid running one subquery for each single event.

Alternate 1: Using UNION using the query above plus another one with m.read = 1. Drawback: Two JOINed heavy queries plus another temporary table.

Alternate 2: SELECT e.* unconditional plus another SELECT event_id, MAX(), COUNT() FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX() and merge everything within the calling Perl script. Drawback: Loosing the ability of a server-side LIMIT.

UPDATE/RESOLUTION

Here is my final query doing the job:

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

The read IS NULL condition caught up events not having any messages in the original query, the read column is boolean NOT NULL.

This query now returns all events and adds the newest unread message timestamp plus the number of unread messages. Both message columns are NULL for events not having any messages which is perfectly ok (will be translated to "" for datetime and 0 for the count on the Perl side).

Thanks to RolandoMySQLDBA, my query is based on his answer.

share|improve this question
    
Is the query you have slow or giving other results than what you want? What indexes do you have on the 2 tables? –  ypercube Feb 21 '12 at 22:05
add comment

2 Answers 2

up vote 2 down vote accepted

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);
share|improve this answer
1  
I think that it'll miss the TAB_EVENT rows that don't have any TAB_MESSAGE rows, but I'll try. –  Sebastian Feb 21 '12 at 19:44
    
The LEFT JOIN will get all rows from TAB_MESSAGE whether it has a corresponding row in TAB_EVENT or not. Is that OK ??? –  RolandoMySQLDBA Feb 21 '12 at 19:49
    
@RolandoMySQLDBA: You changed the order of the tables in the LEFT JOIN. Why? –  ypercube Feb 21 '12 at 21:54
    
@ypercube : I changed the order just to see if it made a difference to Sebastian. I asked if it was OK. In other words, did it generate the desired returned rows or not. I was also hoping to get the 10 keys earlier in the query. If the results set is incomplete, then I know I refactored incorrectly. –  RolandoMySQLDBA Feb 21 '12 at 22:00
    
@ypercube : I edited it !!! –  RolandoMySQLDBA Feb 21 '12 at 22:07
show 2 more comments

Try joining to a subquery:

SELECT e.*, m.datetime AS unread_last, m.id AS unread
  FROM TAB_EVENT e
  LEFT JOIN (
    SELECT event_id, MAX(datetime) AS `datetime`, COUNT(id) AS id
    FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    GROUP BY event_id
  ) m ON e.id=m.event_id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;
share|improve this answer
    
I was thinking similar...without testing it out though, the ORDER BY m.datetime portion doesn't look like it will provide the desired results (as m.datetime is just the max of each event_id) –  Derek Downey Feb 21 '12 at 17:33
    
@DTest: Correct. Is there a different output ordering you're after? –  db2 Feb 21 '12 at 17:49
1  
it's not my question :) –  Derek Downey Feb 21 '12 at 18:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.