Why is WHERE clause with OR so much slower than UNION ALL?

Solution 1:

In MySQL, OR almost always disables use of indexes. UNION is usually a workaround.

Some other vendors do separate queries for each part of the OR, then combine the results before proceeding with the rest of the query. This is complex and costly in its own right. MySQL will, in rare cases, do such. Here is an example: http://mysql.rjweb.org/doc.php/index1

UNION can be faster because of being able to very efficiently use different indexes for the different parts. EXPLAIN may show that the first part very efficiently used workorder_ownersso = '212311642' to find the one (or very few) rows to further check. Suggest that you run the first SELECT of the UNION, plus run EXPLAIN, to see what I mean. (And to see if I am guessing right.)

The other SELECT will see that event_id IS NOT NULL is TRUE and eliminate it. That allows LEFT JOIN to become JOIN. At that point, filtering on event_sso = '212311642' is probably very efficient.

UNION ALL, if certain other conditions apply, can simply deliver rows from the first Select, then deliver rows from the other Select. UNION DISTINCT must gather results from both in a temp table, dedup, and finally deliver the results, hence would be slower.