CODEWITHSUNDEEP
phpmysqlsql
Mattia Dinosaur
Mattia Dinosaur

Reputation: 920

Why the example query with "or" do not use index?

I am reading mysql manual,

Here is a example in the manual .

The example create a Multiple-Column Indexes ,the index is (last_name,first_name)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

the manual said that this query will use index 

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

but the query with or below will not use index :

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Question

why the example query with or , namely ,why 

SELECT * FROM test
      WHERE last_name='Widenius' OR first_name='Michael';

do not use index ?

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Suffice it to say that databases are not good at optimizing OR (or IN or NOT IN) conditions in the WHERE clause.

At a high level, I might describe the reason as the following. When conditions are connecting using AND, the first narrows the population used for the second. This makes indexes feasible because the conditions "nest". When using OR, the conditions are independent. I should note that some databases can handle OR conditions better than others.

If you want your code to use indexes, you can use UNION ALL:

SELECT t.*
FROM test t
WHERE last_name = 'Widenius'
UNION ALL
SELECT t.*
FROM test t
WHERE last_name <> 'Widenius' AND first_name = 'Michael';

For best performance, you want indexes on test(last_name) and test(first_name, last_name).

Upvotes: 2

Related Questions