Tips for Optimizing MySQL Queries

[mandy]

The rule in any situation where you want to opimize some code is that you first profile it and then find the bottlenecks. Mr. Silverton, however, aims right for the tippy top of the trees. I'd say 60% of database optimization is properly understanding SQL and the basics of databases. You need to understand joins vs. subselects, column indices, how to normalize data, etc. The next 35% is understanding the performance characteristics of your database of choice. COUNT(*) in MySQL, for example, can either be almost-free or painfully slow depending on which storage engine you're using. Other things to consider: under what conditions does your database invalidate caches, when does it sort on disk rather than in memory, when does it need to create temporary tables, etc. The final 5%, where few ever need venture, is where Mr. Silverton spends most of his time. Never once in my life have I used SQL_SMALL_RESULT...

full article: http://20bits.com/2007/04/10/10-tips-for...dont-suck/

[misteroriginal]

Yeah, I suppose it's a wonderful collection of tips, but it's like the stupid "tips for dating" advice you see everywhere.

http://www.topdatingtips.com/top-dating-tips.htm

Without context it's meaningless fluff or some annoying form of marketing.

You want to speed up your MySQL queries? I can recommend ten 1000-page books. Ten tips won't do much.