Hi,
!= and <> are not optimized currently because I think it's assumed that
with
a <> b
more rows will NOT match b than do match. Therefore it's faster to do a
table scan.
That assumption is not true in all cases of course, which is why I think
it will be optimized in the future to estimate how many rows will match
and see if the index can be used. Until then, you have to use (a < b OR
a > b) if you want it to be optimized.
Matt
----- Original Message -----
From: <[EMAIL PROTECTED]>
Sent: Thursday, November 20, 2003 10:12 AM
Subject: strange difference between a != b and (a < b OR a > b)
> >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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
