List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 28 1999 3:35pm
Subject:indexes not used with "or" in "where" clause
View as plain text  

>>>>> "Yury" == Yury V Bukhman <yury@stripped> writes:

Yury> Hi all,
Yury> I seem to have a problem with MySQL query optimizer: it does not use
Yury> keys
Yury> in a query where there is an "or" in the "where" clause. Here are the
Yury> indexes from two tables:


mysql> explain
Yury> select yid, first1, first2
Yury> from tab1, tab2
Yury> where tab1.first = tab2.first1
Yury> or tab1.first = tab2.first2;
> +---------+------+---------------+------+---------+------+-------+----------------------------------------------+

Yury> | table   | type | possible_keys | key  | key_len | ref  | rows  |
Yury> Extra                                        |
> +---------+------+---------------+------+---------+------+-------+----------------------------------------------+

Yury> | tab2    | ALL  | first1,first2 | NULL |    NULL | NULL | 20967
Yury> |                                              |
Yury> | tab1    | ALL  | first         | NULL |    NULL | NULL | 35112 | range
Yury> checked for each record (index map: 8) |
> +---------+------+---------------+------+---------+------+-------+----------------------------------------------+


Yury> Does "or" in the "where" clause generally prevent the optimizer from
Yury> using
Yury> indexes?  What might be the way to circumvent this problem?

Note that the 'range checked for each row' ;  This means that MySQL
does use a key on this row! .  It will only check for each row in tab2 
if it's better to scan the rows in tab1 than using the index.  This is 
much slower than direct key references, but MUCH faster than using a
full scan.

Yury> I am using MySQL server version 3.22.25 on VArStation Linux (uname
Yury> gives:
Yury> 2.2.7-1.17smp #1 SMP Mon Jun 21 13:49:43 PDT 1999 i686 unknown)


PS: Sorry for the late reply;  I am still trying to catch up with the
    mails that piled up during my vacation.
indexes not used with "or" in "where" clauseYury V. Bukhman7 Nov
  • Re: indexes not used with "or" in "where" clauseBob Kline7 Nov
    • Re: indexes not used with "or" in "where" clauseYury V. Bukhman11 Nov
  • indexes not used with "or" in "where" clauseMichael Widenius28 Nov