Optimising COUNT(*) out of GROUP_BY queries

Question

Below is a working SQL query that returns a list of unviewed message counts grouped by a user's multiple accounts. However, we don't actually need the counts, just a bit to indicate that unviewed messages exist. Without pulling apart the fairly complex JOIN logic, can you see a way of optimizing the query by replacing COUNT with EXISTS/HAVING/DISCTINCT 1, or some other technique?

I initially thought I could even just replace COUNT with FIRST for some easy optimisation, but no go with MySQL...

(I've looked at this question, but the GROUP BY makes it hard to apply any alternatives I've seen)

SELECT messages_to_user.account_id, COUNT(*) FROM

(SELECT message.id as id, root_message.account_id as account_id
    FROM message
    JOIN message as root_message 
    on message.conversation_id = root_message.id
    AND (root_message.created_by = {user_id}
    OR root_message.to_user_id = {user_id}
    OR root_message.to_user_id IS NULL)
    AND message.created_by != {user_id}
) messages_to_user

LEFT JOIN

(SELECT 
    message_view.id as id, 
    message_view.message_id as message_id,
    message_view.user_id as user_id
    FROM message_view
    WHERE message_view.user_id = {user_id}) viewed_messages

ON messages_to_user.id = viewed_messages.message_id

WHERE viewed_messages.id IS NULL

GROUP BY messages_to_user.account_id

score 1 · Accepted Answer · answered Aug 10 '12 at 16:34

1

If you don't need the count, just omit COUNT(*) from the SELECT on your first line.

I can't promise that this will make your query run faster, but I'm also not convinced that you have any problem that requires effort to be spent on this kind of optimization (where by "this kind" I think I mean "premature").

answered Aug 10 '12 at 16:34

Narveson

1,091
1
9
15

The premature optimization warning kind of has become the standard answer for performance questions. – usr Aug 10 '12 at 16:35
And how do you determine that there *are* unread messages? Just dropping count without replacement just removes that information doesn't it? – usr Aug 10 '12 at 16:45
usr- no it still works- the LEFT JOIN ... WHERE NULL functions as a EXCEPT workaround in MYQSL, so I get a set back that excludes any viewed messages – Yarin Aug 10 '12 at 16:47
Let me clarify: your left join is already filtered down to items in messages_to_user that don't match anything in viewed_messages, so I don't see how you can reduce the effort involved in confirming that there's no match. Subsequently you are trimming your results by merging multiple values of messages_to_user.id that belong to the same messages_to_user.account_id, but this has little to do with the expense of your left join. – Narveson Aug 10 '12 at 16:49
Narveson- you're absolutely right- was making fun of myself for missing the obvious – Yarin Aug 10 '12 at 16:51

score 0 · Answer 2 · answered Aug 10 '12 at 16:34

In SQL Server, I would do this:

case when exists(select * from messages where IsUnread = 1) then 1 else 0 as HasUnreadMessage

This is pseudo-code obviously. Probably you can make it work for MySQL. The exists-check should be much cheaper as it can stop with one row found.

score 0 · Answer 3 · answered Aug 10 '12 at 16:57

How about reducing the size of the subqueries being joined:

SELECT DISTINCT messages_to_user.account_id FROM

(SELECT DISTINCT message.id as id, root_message.account_id as account_id
    FROM message
    JOIN message as root_message 
    on message.conversation_id = root_message.id
    AND (root_message.created_by = {user_id}
    OR root_message.to_user_id = {user_id}
    OR root_message.to_user_id IS NULL)
    AND message.created_by != {user_id}
) messages_to_user

LEFT JOIN

(SELECT DISTINCT message_id
    FROM message_view
    WHERE message_view.user_id = {user_id}) viewed_messages

ON messages_to_user.id = viewed_messages.message_id

WHERE viewed_messages.message_id IS NULL

Optimising COUNT(*) out of GROUP_BY queries

3 Answers3