[development] Guidelines for writing efficient SQL code

[Jamie Holly]

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/
>
>
>
>