>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?
>How-To-Repeat:
see above
>Fix:
have slower non-key queries :(
>Submitter-Id: sklar@stripped
>Originator: MySQL User
>Organization: Student.Net Publishing
>MySQL support: extended email support
>Synopsis: keys not used on query with OR
>Severity: serious
>Priority: low
>Category: mysql
>Class: change-request
>Release: mysql-3.22.14-gamma (TCX binary)
>Environment:
System: Linux host 2.0.35 #1 Tue Aug 18 23:43:14 EDT 1998 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.7.2.3/specs
gcc version 2.7.2.3
Compilation info: CC='gcc' CFLAGS='-O6 -mpentium -mstack-align-double
- -fomit-frame-pointer' CXX='gcc' CXXFLAGS='-O6 -mpentium
-mstack-align-double
- -fomit-frame-pointer -felide-constructors' LDFLAGS='-static'
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=TCX
binary' --enable-assembler --with-mysqld-ldflags=-all-static
- --with-client-ldflags=-all-static --disable-shared
Perl: This is perl, version 5.004_04 built for i386-linux
