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