Hi, >From the MySQL Manual:
MySQL normally uses the index that finds the least number of rows. An index is used for columns that you compare with the following operators: =, >, >=, <, <=, BETWEEN, and a LIKE with a non-wildcard prefix like 'something%'. http://www.mysql.com/doc/en/MySQL_indexes.html The != does not belong to that list of comparision operators... Hope this helps! -- Diana Soares On Thu, 2003-11-20 at 16:12, [EMAIL PROTECTED] wrote: > >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' -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
