Justin Silverton at Jaslabs has a supposed list of 10 tips for optimizing MySQL queries. I couldn't read this and let it stand because this list is really, really bad. Some guy named Mike noted this, too. So in this entry I'll do two things: first, I'll explain why his list is bad; second, I'll present my own list which, hopefully, is much better. Onward, intrepid readers!
10 Tips for Optimizing MySQL Queries (That don’t suck) | 20bits
Popularity Report
 |
|||
|
|||
|
|||
|
|||
|
|||
|
Rank
URL Tag Cloud
- mysql
- , performance
- , optimization
- , sql
- , programming
- , database
- , development
- , optimizing
- , scaling
- , dont
- , queries
- , 10
- , optimisation
- , 20bits
- , suck)
- , example
- , open
- , howto
Bookmark History
Saved by 12 people (4 private), first by anonymouse user on 2007-04-10
- Cwimsatt on 2008-05-19 - Tags Taxes
- Navneetk on 2008-04-08 - Tags MySQL , Performance , Optimization
- Smoody on 2008-03-31 - Tags example , mysql , optimizing , performance , scaling
- Joel_test3 on 2008-03-18 - Tags mysql , performance
- Liuyong5 on 2008-03-18 - Tags mysql , performance
Public Sticky notes
He's swinging for the top of the trees
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.
Highlighted by joel
Some of his suggestions are just mind-boggling, e.g., "remove unnecessary paratheses." It really doesn't matter whether you do SELECT * FROM posts WHERE (author_id = 5 AND published = 1) or SELECT * FROM posts WHERE author_id = 5 AND published = 1. None. Any decent DBMS is going to optimize these away. This level of detail is akin to wondering when writing a C program whether the post-increment or pre-increment operator is faster. Really, if that's where you're spending your energy, it's a surprise you've written any code at all
Highlighted by joel
Public Comment
on 2007-04-11 by knightnet