[development] Guidelines for writing efficient SQL code

[Narayan Newton]

Quoted integers are typecast from string back to integers. If they
weren't, the index wouldn't be usable.

If you notice a speed improvement in quoting an integer, either the
benchmark is invalid for some reason (did you test the quoted version
after the regular int version? Was it in the query cache? Was it in
the buffer pool already?) or your in a more complicated situation
where quoting the integer is forcing you not to use the index, and the
index has a very low cardinality. That is very unlikely, so I'd guess
your benchmark is flawed.

On Wed, Aug 26, 2009 at 2:46 PM, Jamie Holly<hovercrafter at earthlink.net> wrote:
> I actually tried this on MySQL 5.0.17 using MyISAM. I tried it on primary,
> standard index and no index columns and restarted MySQL between each run and
> really didn't notice any different in a comments table with about 1.5
> million rows (at the most .02ms and it would very in either direction -
> quoted or none). I wonder if there was a release out there where this may
> have been broken in MySQL.
>
> Jamie Holly
> http://www.intoxination.net http://www.hollyit.net
>
>
>
> Earnie Boyd wrote:
>>
>> Quoting Nancy Wichmann <nan_wich at bellsouth.net>:
>>
>> > Pierre Rineau wrote:
>> >> If you test a integer field, use no quotes, if you test a varchar
>> >> field,
>> >> use quotes, that's it. Your DBMS will be smarter than you (except
>> >> MySQL,
>> >> but for kitten sake, do standard SQL, it will be easier for PostgreSQL
>> >> user like me to port your code if needed).
>> >
>> > Then why, Pierre, is the quoted version faster, by far?  At least in
>> > MySql.
>> >
>> > Please stop disparaging MySql. The vast majority of sites use it, so it
>> > can't be all bad.
>> >
>>
>> It makes no sense to me why a quoted numeric string is faster than a
>>  native integer unless the database engine is broken.  Not talking  about
>> MySQL vs anything else but integer comparison should be faster  than string
>> comparison.  My guess is that the database engine is  converting the integer
>> to string when storing it in the index file but  I have no proof of that.
>>  Have you tried your testing with InnoDB?
>>
>> --
>> Earnie
>> -- http://r-feed.com/           -- http://for-my-kids.com/
>> -- http://www.4offer.biz/       -- http://give-me-an-offer.com/
>>
>>
>>
>>
>



-- 
Narayan Newton
GA Member Drupal Association
Tag1Consulting