>Description:
a simple select on a large table does not use an indexed column when the WHERE
clause uses a != b
The same query using as WHERE a < b OR a > b (which of course does the same)
*does* use an index.
>How-To-Repeat:
mysql <<END_OF_FILE
create database unequalproblem;
use unequalproblem;
-- MySQL dump 8.22
--
-- Host: localhost Database: unequalproblem
---------------------------------------------------------
-- Server version 3.23.56
--
-- Table structure for table 'test'
--
CREATE TABLE test (
id int(11) NOT NULL auto_increment,
number int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY k_number (number)
) TYPE=MyISAM;
--
-- Dumping data for table 'test'
--
INSERT INTO test VALUES (1,2);
INSERT INTO test VALUES (2,4);
INSERT INTO test VALUES (3,9);
select('EXPLAIN SELECT id from test WHERE number != 1; *** uses NO index');
EXPLAIN SELECT id from test WHERE number != 1;
select('EXPLAIN SELECT id from test WHERE number < 1 OR number > 1; *** actually
same query, index on number');
EXPLAIN SELECT id from test WHERE number < 1 OR number > 1;
drop database unequalproblem
END_OF_FILE
>Fix:
the work around is in the prblem description
>Submitter-Id: <submitter ID>
>Originator: [EMAIL PROTECTED]
>Organization:
European Design Centre b.v.
>MySQL support: none
>Synopsis: "WHERE a != b" evaluates different from "WHERE a < b OR a > b"
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.58 (Source distribution)
>Environment:
System: Linux archie 2.4.20-20.9 #1 Mon Aug 18 11:27:43 EDT 2003 i686 athlon i386
GNU/Linux
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/3.2.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -march=i386
-mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'
CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686
-D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions'
LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 May 15 2003 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x 1 root root 1557296 Apr 8 2003 /lib/libc-2.3.2.so
-rw-r--r-- 1 root root 2331360 Apr 8 2003 /usr/lib/libc.a
-rw-r--r-- 1 root root 204 Apr 8 2003 /usr/lib/libc.so
Configure command: ./configure '--host=i386-redhat-linux' '--build=i386-redhat-linux'
'--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr'
'--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc'
'--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib'
'--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com'
'--mandir=/usr/share/man' '--infodir=/usr/share/info' '--without-readline'
'--without-debug' '--enable-shared' '--with-extra-charsets=complex' '--with-bench'
'--localstatedir=/var/lib/mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--with-mysqld-user=mysql' '--with-extra-charsets=all' '--with-innodb'
'--enable-local-infile' '--enable-large-files=yes' '--enable-largefile=yes'
'--with-berkeley-db-includes=/usr/include' '--with-berkeley-db-libs=/usr/lib'
'--enable-thread-safe-client' 'CFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686
-D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' '!
CXXFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64
-D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'build_alias=i386-redhat-linux'
'host_alias=i386-redhat-linux' 'target_alias=i386-redhat-linux-gnu'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
