Stack Stack - 5 months ago 13
SQL Question

Can MySQL use Indexes when there is OR between conditions?

I have two queries plus its own

EXPLAIN
's results:

One:

SELECT *
FROM notifications
WHERE id = 5204 OR seen = 3


enter image description here

Benchmark (for 10,000 rows): 0.861




Two:

SELECT h.* FROM ((SELECT n.* from notifications n WHERE id = 5204)
UNION ALL
(SELECT n.* from notifications n WHERE seen = 3)) h


enter image description here

Benchmark (for 10,000 rows): 2.064




The result of two queries above is identical. Also I have these two indexes on
notifications
table:

notifications(id) -- this is PK
notification(seen)


As you know,
OR
usually prevents effective use of indexes, That's why I wrote second query (by
UNION
). But after some tests I figured it out which still using
OR
is much faster that using
UNION
. So I'm confused and I really cannot choose the best option in my case.

Based on some logical and reasonable explanations, using
union
is better, but the result of benchmark says using
OR
is better. May you please help me should I use which approach?

Answer

The query plan for the OR case appears to indicate that MySQL is indeed using indexes, so evidently yes, it can do, at least in this case. That seems entirely reasonable, because there is an index on seen, and id is the PK.

Based on some logical and reasonable explanations, using union is better, but the result of benchmark says using OR is better.

If "logical and reasonable explanations" are contradicted by reality, then it is safe to assume that the logic is flawed or the explanations are wrong or inapplicable. Performance is notoriously difficult to predict; performance testing is essential where speed is important.

May you please help me should I use which approach?

You should use the one that tests faster on input that adequately models that which the program will see in real use.

Note also, however, that your two queries are not semantically equivalent: if the row with id = 5204 also has seen = 3 then the OR query will return it once, but the UNION ALL query will return it twice. It is pointless to choose between correct code and incorrect code on any basis other than which one is correct.

Comments