[development] Guidelines for writing efficient SQL code
hovercrafter at earthlink.net
Wed Aug 26 22:15:04 UTC 2009
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.
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/
More information about the development