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.
Shouldn't MySQL just cast the constant integer to a string instead? Perhaps this optimization isn't done. Also, I'm not completely sure, but I think this type of query was indexed in 3.23. Or more precisely, these queries didn't become "slow" until after I upgraded to 4.0.18 (from 3.23.40). Granted, ultimately I needed (and did) change the column type, but I'm curious to see if & why the behavior changed.
Could someone running 3.23 check the output of:
explain select * from bar where foo=1 (1 without quotes) explain select * from bar where foo='1' (1 with quotes) (foo is an indexed varchar column)
Is only one indexed, or are they both indexed?
- Max
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
