I'm just wondering if there is any improvements that can be made on my queries, as i've looked around and posts are saying to use UNION ALL
instead of OR
but with the performance times it looking like the OR
condition works out better.
Could anyone please give me some advise on improving my queries?
Using UNION ALL:
Time taken: Showing rows 0 - 1 (2 total, Query took 0.0016 seconds.)
(SELECT direct_message.*
FROM `direct_message`
INNER JOIN direct_message_thread ON direct_message_thread.chat_id = direct_message.id
WHERE direct_message.recipient_id = '10896'
AND direct_message.school_id = '1'
AND direct_message_thread.school_id = direct_message.school_id
GROUP BY direct_message.id
ORDER BY direct_message_thread.inserted DESC)
UNION ALL
(SELECT direct_message.*
FROM `direct_message`
INNER JOIN direct_message_thread ON direct_message_thread.chat_id = direct_message.id
WHERE direct_message.creator_id = '10896'
AND direct_message.school_id = '1'
AND direct_message_thread.school_id = direct_message.school_id
GROUP BY direct_message.id
ORDER BY direct_message_thread.inserted DESC)
UNION ALL
(SELECT direct_message.*
FROM `direct_message`
LEFT JOIN direct_message_group ON direct_message_group.chat_id = direct_message.id
INNER JOIN direct_message_thread ON direct_message_thread.chat_id = direct_message.id
WHERE direct_message_group.staff_id = '10896'
AND direct_message.school_id = '1'
AND direct_message_thread.school_id = direct_message.school_id
GROUP BY direct_message.id
ORDER BY direct_message_thread.inserted DESC)
EXPLAIN OF QUERY
Using OR:
Time taken: Showing rows 0 - 1 (2 total, Query took 0.0011 seconds.)
SELECT direct_message.*,
IFNULL(direct_message_group.staff_id, 0) AS staff_id
FROM `direct_message`
LEFT JOIN direct_message_group ON direct_message_group.chat_id = direct_message.id
INNER JOIN direct_message_thread ON direct_message_thread.chat_id = direct_message.id
WHERE ((direct_message.recipient_id = '10896'
OR direct_message.creator_id = '10896'
OR (direct_message_group.staff_id = '10896'
AND direct_message_group.active = '0')))
AND direct_message.school_id = '1'
AND direct_message_thread.school_id = direct_message.school_id
GROUP BY direct_message.id
ORDER BY direct_message_thread.inserted DESC
LIMIT 0,
25