From: Jeff Smelser
Date: October 19 2005 8:17pm
Subject: Re: implicit cast forces table scan?
List-Archive: http://lists.mysql.com/mysql/190591
Message-Id: <200510191517.19775.tradergt@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed;
  boundary="nextPart3138838.OeoXd9M7cM";
  protocol="application/pgp-signature";
  micalg=pgp-sha1
Content-Transfer-Encoding: 7bit

--nextPart3138838.OeoXd9M7cM
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 =3D "170807";
>
> This query performs a table scan:
> mysql> explain
>     -> select itran_log_date, itran_log_actionid from itran_log where
>     -> itran_log_actionid =3D 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 convertin=
g=20
the data, you could have "17h120", and it would fail converting the data.=20
Mysql, nor any DB for that matter, should not, and do not, assume anything.=
=20
It just happens in the case your dealing with numeric data. If thats the=20
case, you should have made the column numeric in type. (int whatever)

Jeff

Jeff

--nextPart3138838.OeoXd9M7cM
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBDVqnPoOk9EvUvEtgRArmyAJ92JLgbbjGdiiIWNeJMt2JOr4hghwCg4iEW
qa/VghnUIFhtkboTG48/t/I=
=+prH
-----END PGP SIGNATURE-----

--nextPart3138838.OeoXd9M7cM--