List:General Discussion« Previous MessageNext Message »
From:Jeff Smelser Date:October 19 2005 8:17pm
Subject:Re: implicit cast forces table scan?
View as plain text  
On Wednesday 19 October 2005 01:15 pm, Olaf Faaland wrote:
> 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 query performs a table scan:
> mysql> explain
>     -> select itran_log_date, itran_log_actionid from itran_log where
>     -> itran_log_actionid = 170807;
>
> 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?  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.
>
> 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.

It doesnt know what value your giving it. If it thought to assume converting 
the data, you could have "17h120", and it would fail converting the data. 
Mysql, nor any DB for that matter, should not, and do not, assume anything. 
It just happens in the case your dealing with numeric data. If thats the 
case, you should have made the column numeric in type. (int whatever)

Jeff

Jeff

Attachment: [application/pgp-signature]
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