sql - Can MySQL use Indexes when there is OR between conditions?

Question

I have two queries plus its own EXPLAIN's results:

One:

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

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 

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 1

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.