List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:October 21 2005 4:42am
Subject:Re: implicit cast forces table scan?
View as plain text  
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
Thread
implicit cast forces table scan?Olaf Faaland19 Oct
  • Re: implicit cast forces table scan?Jeff Smelser19 Oct
  • Re: implicit cast forces table scan?Michael Stassen21 Oct
  • Re: implicit cast forces table scan?Gleb Paharenko21 Oct
    • Re: implicit cast forces table scan?Michael Stassen21 Oct
      • Re: implicit cast forces table scan?Gleb Paharenko23 Oct
        • Re: implicit cast forces table scan?Michael Stassen24 Oct
RE: implicit cast forces table scan?Olaf Faaland21 Oct