List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 1 1999 8:44pm
Subject:keys not used on query with OR
View as plain text  
>>>>> "David" == David Sklar <sklar@stripped> writes:

>> Description:
David> 	I create the following table:

<cut>

mysql> EXPLAIN SELECT * FROM key_test WHERE  a = 13 OR b = 13;
David> +----------+------+---------------+------+---------+------+-------+------------
David> +
David> | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra      
David> |
David> +----------+------+---------------+------+---------+------+-------+------------
David> +
David> | key_test | ALL  | a,b           | NULL |    NULL | NULL | 11450 | where used 
David> |
David> +----------+------+---------------+------+---------+------+-------+------------
David> +


David> I looked through the changelog but there's no mention (i could find)
David> of anything that might correct this behavior in future versions.

David> Is there a way to do this query over multiple columns and still have keys used?

>> How-To-Repeat:
David> 	see above
>> Fix:
David> 	have slower non-key queries :(

Hi!

The problem is that a general OR key optimizer is MUCH harder to write
than an AND optimizer (the MySQL AND optimizer is very general and
very efficient).  When UNIONS are implemented. we will probably start
by writing an optimizer for the simple cases before trying on a
general one.

In MySQL 3.23.2 you can do this VERY efficiently with:

CREATE TEMPORARY TABLE tmp (unique key (a,b,c)) TYPE=HEAP SELECT a,b,c FROM key_test
WHERE a = 13;
INSERT IGNORE INTO tmp SELECT a,b,c FROM key_test WHERE b = 10;
SELECT * from tmp;
DELETE tmp;

Regards,
Monty
Thread
keys not used on query with ORDavid Sklar21 Jul
  • Re: keys not used on query with ORJim Faucette21 Jul
  • keys not used on query with ORMichael Widenius2 Aug