I have two queries plus its own
WHERE id = 5204 OR seen = 3
SELECT h.* FROM ((SELECT n.* from notifications n WHERE id = 5204)
(SELECT n.* from notifications n WHERE seen = 3)) h
notifications(id) -- this is PK
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
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.