Question 20167 in Mysql Question posted in
The official MySQL documentation can be found here.

why is a multi-column index not used for a sql with OR condition? – Mysql

I was reading MySQL document when I found that it said
SELECT * FROM test WHERE last_name='Jones' AND first_name='John';
will use index, while
SELECT * FROM test WHERE last_name='Jones' OR first_name='John';
will not.
Could somebody tell me the reason why the second sql does not use index? As last_name and first_name are all in the index and they match the "leftmost prefix of the index".The only difference between the two sqls is AND and OR.

Tags:

2

Answers


  1. 0

    Your question is easy to answer with an understanding of how B-trees work. This answer assumes that you have an index on (last_name, first_name) or (first_name, last_name). In the case of the first AND query:

    SELECT * FROM test WHERE last_name = 'Jones' AND first_name = 'John';

    MySQL can do an index seek for the first name, followed by the last name (or the opposite order), and isolate a subtree of the original B-tree corressponding to the matching records.

    In the case of the second OR query:

    SELECT * FROM test WHERE last_name = 'Jones' OR first_name = 'John';

    This query cannot be satisfied by a single seek of the B-tree index. Rather, multiple seeks would be needed, because either the first or last name criteria might be true. In practice, if MySQL wanted to use an index in this case, it would have to scan the index instead. A scan might not be any more performant than just hitting the test table directly, and hence MySQL may not opt to use the index at all.

    Login or Signup to reply.
  2. 0

    Perhaps the more canonical phone book example might also help visualize:

    Imagine a phone book (remember those ancient monolithic things, possibly not!) and you want to find the phone numbers for everyone named John Jones.

    The phone book is essentially a big covering index (it has not only the names but the numbers and other details too), organised by Last name, First name. By requiring to find both names you can simply dive straight in alphabetically at the first "Jones" and then start reading through all the "Johns" in order.

    Now consider if you wanted instead to find all those with a last name of "Jones" or all those with a first name of "John".

    The phone book is sorted by last name so you could still dive right in and find all the "Jones", but how are you going to find all those named "John"? They can be literally anywhere in the entire book, so making use of how the names in the phone book are organised is essentially useless, you have no choice but to start on page 1 and scan down every entry to find those named John.

    And since you have to do that anyway, there’s no point in separately searching for just the surname since you’re going to be scanning your eye down every page regardless, you may as well do both together.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search