![]() ![]() apply LIMIT 10 against the collected rows from TAB_MESSAGE.only collect necessary columns from TAB_MESSAGE.Maybe try refactoring the query in such a way that in executes in this sequence Here is your original query from the question SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread Thanks to RolandoMySQLDBA, my query is based on his answer. 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). This query now returns all events and adds the newest unread message timestamp plus the number of unread messages. ![]() The read IS NULL condition caught up events not having any messages in the original query, the read column is boolean NOT NULL. SELECT event_id,MAX(`datetime`) AS unread_last, COUNT(*) AS unread FROM TAB_MESSAGE Here is my final query doing the job: SELECT e.*, m.unread_last, m.unread read = 1.ĭrawback: Two JOINed heavy queries plus another temporary table.Īlternate 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.ĭrawback: Loosing the ability of a server-side LIMIT. 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.Īlternate 1: Using UNION using the query above plus another one with m. 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. 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). WHERE ( m.`read` IS NULL OR m.`read` = 0) LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id ![]() This is my current mySQL query: SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |