>>>>> "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
