Hi, You are correct; it was an error that the column was defined as a VARCHAR. I started out without quotes in the SELECT because the application that uses this db has been running successfully, so I believed the column types to be OK. Now I know better.
Once I realized the problem was with the data types, I verified the data was all numeric, and changed the column to an INT. I was still mystified by the cast being done on each row, instead of being done once on the value given in the select statement. Thank you very much for the careful explanation. -Olaf > -----Original Message----- > From: Michael Stassen [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 20, 2005 9:42 PM > To: Olaf Faaland > Cc: 'mysql@lists.mysql.com' > Subject: Re: implicit cast forces table scan? > > > Olaf Faaland wrote: > > Hi, > > > > I am currently using mysql 4.0.18 as distributed with red > hat Linux. I find > > when I perform a select on a varchar(30) field, the index > is used only if I > > have quoted the value in the where clause. Otherwise, > mysql performs a > > table scan. > > A varchar(30) field is a string, so only comparisons to > strings really make > sense. If you don't quote the comparison value in the WHERE > clause, you're > asking for trouble. First, the unquoted value must make > sense. There are 3 > possibilities: it's a number, it's a function, or it's a column name. > Hence, you can get away with > > WHERE varchar_field = 170807 > > but probably not > > WHERE varachar_field = dogs > > (unless you have a column named "dogs"). > > I expect you know all that. My point is, why aren't you quoting the > comparison string? > > > The queries in question are: > > > > This query uses the index: > > mysql> explain > > -> select itran_log_date, itran_log_actionid from > itran_log where > > -> itran_log_actionid = "170807"; > > This is a string column compared to a string constant, so the > index can be used. > > > This query performs a table scan: > > mysql> explain > > -> select itran_log_date, itran_log_actionid from > itran_log where > > -> itran_log_actionid = 170807; > > This is a string compared to a number. You are asking mysql > to make an > implicit conversion so it can make the comparison. You > expected the number > to be converted to a string, but that's not how it works -- > it's the other > way around, the string is converted to a number. Why? > Because many strings > convert to the same number. Consider: > > mysql> SELECT '17' + 0, '17.0' + 0, '+17' + 0, '17,345' + 0, > '17 dogs' + 0; > +----------+------------+-----------+--------------+---------------+ > | '17' + 0 | '17.0' + 0 | '+17' + 0 | '17,345' + 0 | '17 dogs' + 0 | > +----------+------------+-----------+--------------+---------------+ > | 17 | 17 | 17 | 17 | 17 | > +----------+------------+-----------+--------------+---------------+ > 1 row in set (0.00 sec) > > The map from string to number is many-to-one, so the only > safe course is to > convert the string to a number, then do a numeric comparison. > Of course, > that renders the string index useless, so the full table scan > is required. > > > My question is this: is the issue here that mysql is > converting every single > > itran_log_actionid value, from all 1.5 million rows, and > hence the index is > > not useful and not used? > > Yes. > > > My initial assumption was that the constant value > > 170807 in the second query, would be converted to text > before the query was > > executed, and so the index could be used. This does not > seem to be the > > case. > > Right, it's not. > > > I ask both for my own edification, and also because it > seems to me this > > should be mentioned in the manual for newbies like myself. > > I thought it was mentioned in the manual, though I must admit > I didn't find > it just now after a brief search. > > I'm wondering, however, if we're really approaching this from > the right > angle. Your column is named itran_log_actionid, and it seems > to contain > numbers such as 170807. The obvious question, then, is why is > itran_log_actionid a VARCHAR(30) instead of one of the INT > types? If it > were, you wouldn't need to quote the constant in the WHERE > clause, and the > comparison would be numeric (faster than string) with no type > conversion > > > thanks, > > Olaf > > Michael > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
