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

I have a very complex database structure and I cannot make a minimal reproducable example because this would not show the performance problem this question is about. So please be sorry if my question is not precise enough. But maybe you see something directly which I am not seeing. So I will try to explain:

When I do this query:

SELECT workorder_id AS job_id
            FROM ep_jobworkorders 
            LEFT JOIN ep_jobevents ON (workorder_id = event_workorderid)
            WHERE (event_id IS NULL AND workorder_ownersso = '212311642' AND workorder_start <= '2021-06-27' AND workorder_end >= '2021-06-21')
        OR (event_id IS NOT NULL AND event_workorderid IS NOT NULL AND event_sso = '212311642' AND  event_start <= '2021-06-27' AND event_end  >= '2021-06-21')

it is around 25 times slower then doing this query

SELECT workorder_id AS job_id
            FROM ep_jobworkorders 
            LEFT JOIN ep_jobevents ON (workorder_id = event_workorderid)
            WHERE (event_id IS NULL AND workorder_ownersso = '212311642' AND workorder_start <= '2021-06-27' AND workorder_end >= '2021-06-21')
union all
SELECT workorder_id AS job_id
            FROM ep_jobworkorders 
            LEFT JOIN ep_jobevents ON (workorder_id = event_workorderid)
            WHERE (event_id IS NOT NULL AND event_workorderid IS NOT NULL AND event_sso = '212311642' AND  event_start <= '2021-06-27' AND event_end  >= '2021-06-21')

All columns used in ON and WHERE are indexed.

So, why is it so much slower to use the OR in the WHERE clause? For me this makes no sense.

CodePudding user response：

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.