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
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").