Hi!
>>>>> "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:
<cut>
mysql> explain
Yury> select yid, first1, first2
Yury> from tab1, tab2
Yury> where tab1.first = tab2.first1
Yury> or tab1.first = tab2.first2;
Yury>
> +---------+------+---------------+------+---------+------+-------+----------------------------------------------+
Yury> | table | type | possible_keys | key | key_len | ref | rows |
Yury> Extra |
Yury>
> +---------+------+---------------+------+---------+------+-------+----------------------------------------------+
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>
> +---------+------+---------------+------+---------+------+-------+----------------------------------------------+
<cut>
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)
Regards,
Monty
PS: Sorry for the late reply; I am still trying to catch up with the
mails that piled up during my vacation.
