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

[Max Campos]

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]