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