List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:July 20 1999 10:55pm
Subject:Re: keys not used on query with OR
View as plain text  
David Sklar wrote:
> 
> >Description:
>         I create the following table:
> 
> CREATE TABLE key_test (
>   a smallint unsigned not null,
>   b smallint unsigned not null,
>   KEY (a),
>   KEY (b)
> );
> 
> and fill it with about 10k or 15k rows of random integers. Queries on
> either column use the appropriate key:
> 
> mysql> EXPLAIN SELECT * FROM key_test WHERE  b = 13;
> +----------+------+---------------+------+---------+------+------+-------+
> | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
> +----------+------+---------------+------+---------+------+------+-------+
> | key_test | ref  | b             | b    |       2 | ???  |    1 |       |
> +----------+------+---------------+------+---------+------+------+-------+
> 
> mysql> EXPLAIN SELECT * FROM key_test WHERE  a = 13;
> +----------+------+---------------+------+---------+------+------+-------+
> | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
> +----------+------+---------------+------+---------+------+------+-------+
> | key_test | ref  | a             | a    |       2 | ???  |    1 |       |
> +----------+------+---------------+------+---------+------+------+-------+
> 
> but a query on both columns doesn't:
> 
> mysql> EXPLAIN SELECT * FROM key_test WHERE  a = 13 OR b = 13;
> +----------+------+---------------+------+---------+------+-------+------------
> +
> | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra
> |
> +----------+------+---------------+------+---------+------+-------+------------
> +
> | key_test | ALL  | a,b           | NULL |    NULL | NULL | 11450 | where used
> |
> +----------+------+---------------+------+---------+------+-------+------------
> +
> 
> I looked through the changelog but there's no mention (i could find)
> of anything that might correct this behavior in future versions.
> 
> Is there a way to do this query over multiple columns and still have keys used?
> 

Your problem has 2 parts. 1st, only one key will be used per table. 2nd,
with the OR even if it used key 'a' it would still do a full table read
for b -- therefore it uses no key.

One solution is to add another key across 'a' and 'b'.

  jim....
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