Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can MySQL use Indexes when there is OR between conditions?

Tags:

sql

mysql

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?

like image 296
Martin AJ Avatar asked Jun 09 '16 15:06

Martin AJ


People also ask

Under what conditions should the indexes avoid?

Indexes should not be used on tables containing few records. Tables that have frequent, large batch updates or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Indexes should not be used on the columns that are frequently manipulated.

Why index is not used in MySQL?

The Benefits and Drawbacks of Using Indexes in MySQL Indexes consume disk space. Indexes degrade the performance of INSERT, UPDATE and DELETE queries – when data is updated, the index needs to be updated together with it. MySQL does not protect you from using multiple types of indexes at the same time.

What are disadvantages of using indexes in MySQL?

The Drawbacks of Using Indexes Indexes consume disk space – an index occupies its own space, so indexed data will consume more disk space too; Redundant and duplicate indexes can be a problem – MySQL allows you to create duplicate indexes on a column and it does not “protect you” from doing such a mistake.

Can we use index in WHERE clause?

You should always add an index on any field to be used in a WHERE clause (whether for SELECT, UPDATE, or DELETE). The type of index depends on the type of data in the field and whether you need each row to have a unique value.


1 Answers

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.

like image 123
John Bollinger Avatar answered Dec 05 '22 13:12

John Bollinger