[development] Guidelines for writing efficient SQL code

[Jamie Holly]

I didn't notice any real speed difference (it was minute and could be 
contributed to anything). I restarted the server daemon between each run 
and I keep caching off on my devel server just for better optimization 
during development as it is. Thats on Ubuntu 9.04 64 bit, dual core 
2.2ghz running 4 gigs.

I just went ahead and ran them with query cache on and saw no difference 
between quoted or unquoted integers. The only thing I did notice (I ran 
each twice on NID in comments) was that the query cache doesn't kick in 
if you change the field from non quote to quote in the where clause. It 
apparently views them as different queries, which is a good thing.

My original tests were on PID and NID. For the no index test I just 
dropped the NID index.

Jamie Holly
http://www.intoxination.net 
http://www.hollyit.net



Narayan Newton wrote:
> 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/
> >>
> >>
> >>
> >>
> >
>
>
>
>