[development] Guidelines for writing efficient SQL code
hovercrafter at earthlink.net
Wed Aug 26 21:46:44 UTC 2009
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.
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?
> -- http://r-feed.com/ -- http://for-my-kids.com/
> -- http://www.4offer.biz/ -- http://give-me-an-offer.com/
More information about the development