MySQL queries to select messages related to a user, using UNION vs. OR

Question

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

enter image description here

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

EXPLAIN OF QUERY enter image description here

Answer 1

OR is really bad in Joins, in WHERE it can/should be replaced when the optimizer doesn't use indexes (and OR does).

I would prefer the Union for a simple reason, it's easier to write/understand/extend. Additionally the first two Selects are the same besides one condition, so combining them is simple.

And using aliases really simplifies reading the code.

 (SELECT dm.*
 FROM `direct_message`AS dm
 INNER JOIN direct_message_thread AS dmt
 ON dmt.chat_id = dm.id
 AND dmt.school_id = dm.school_id -- moved from WHERE as this is a join condition 
 WHERE (dm.recipient_id = '10896' OR dm.creator_id = '10896')
 AND dm.school_id = '1'
 GROUP BY dm.id
 ORDER BY dmt.inserted DESC)
UNION ALL
 (SELECT dm.*
 FROM `direct_message` AS dm
 INNER JOIN direct_message_thread AS dmt
 ON dmt.chat_id = dm.id
 AND dmt.school_id = dm.school_id
 INNER JOIN direct_message_group AS dmg
 ON dmg.chat_id = dm.id
 WHERE dmg.staff_id = '10896'
 AND dm.school_id = '1'
 GROUP BY dm.id
 ORDER BY dmt.inserted DESC)