List:General Discussion« Previous MessageNext Message »
From:David Sklar Date:July 20 1999 9:17pm
Subject:keys not used on query with OR
View as plain text  
>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



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