Re: Strange Index Usage: select ... where foo = 90 on a varchar

[Michael Stassen]

You shouldn't be surprised.  This is normal behavior.  interchangeStatus is 
a varchar, so

  select fileName from outDocInterchange where interchangeStatus = 91;

requires that interchangeStatus be converted to an int for each row so it 
can be compared to 91, rendering the index useless.  On the other hand,

  select fileName from outDocInterchange where interchangeStatus =  '91';

compares interchangeStatus to a string, which the index is designed to do. 
In general, an index on a column won't help if the column is input to a 
function.

Michael

Max Campos wrote:

Lucy, you've got some EXPLAINing to do....... (sorry, couldn't resist)

A) select fileName from outDocInterchange where interchangeStatus = 91;
B) select fileName from outDocInterchange where interchangeStatus =  '91';
(A) Runs unindexed, (B) runs with the istat_date index.  Can anyone  
explain why?

My table (other columns/keys removed):
Create Table: CREATE TABLE `outDocInterchange` (
  `dateReceived` datetime default '0000-00-00 00:00:00',
  `interchangeStatus` varchar(20) default NULL,
  KEY `istat_date` (`interchangeStatus`,`dateReceived`),
) TYPE=MyISAM
Obviously I need to change interchangeStatus to an int, but I was still  
suprised to see the results:

mysql> explain select fileName from outDocInterchange where  
interchangeStatus = 91;
+-------------------+------+---------------+------+---------+------ 
+-------+-------------+
| table             | type | possible_keys | key  | key_len | ref  |  
rows  | Extra       |
+-------------------+------+---------------+------+---------+------ 
+-------+-------------+
| outDocInterchange | ALL  | istat_date    | NULL |    NULL | NULL |  
37223 | Using where |
+-------------------+------+---------------+------+---------+------ 
+-------+-------------+
1 row in set (0.08 sec)

mysql> explain select fileName from outDocInterchange where  
interchangeStatus = '91';
+-------------------+------+---------------+------------+--------- 
+-------+------+-------------+
| table             | type | possible_keys | key        | key_len | ref  
  | rows | Extra       |
+-------------------+------+---------------+------------+--------- 
+-------+------+-------------+
| outDocInterchange | ref  | istat_date    | istat_date |      21 |  
const |    1 | Using where |
+-------------------+------+---------------+------------+--------- 
+-------+------+-------------+
1 row in set (0.08 sec)

I'm using MySQL 4.0.18 for Solaris 8.

Can anyone explain this?  Or is this a bug (or missing optimization)?

Thanks.

- Max




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]