On Thu, 15 Apr 2004, Max Campos wrote: > On Apr 13, 2004, at 2:01pm, Michael Stassen wrote: > > > 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.
Except there are multiple ways that something that is numerically equal to 91 can be represented as a string, eg. "91.0", " 91", etc. So using the index would result in different behaviour in some situations. I think that if things worked how I would like them, mysql wouldn't automatically do the cast at all so it would be obvious that something that is possibly unexpected is happening. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
