ben robison

MySQL Optimization

I’ve been doing some reading recently for a (long procrastinated) assignment at school. I’ve found some official looking materials as well as a recent Digg that led to some generally accepted industry expertise. I’ll give the links in a moment, but here’s the point of it all.

Low-hanging fruit
Don’t waste time trying to tune every query in your database. It can’t be done. Find the ones that take all the time and tune them. The slow queries log is a good place to start. My understanding is that you can set the parameters for the slow queries log in your my.cnf file.

log_slow_queries=/var/lib/mysql/slow­queries.log
long_query_time=2
log_long_format

EXPLAIN
Once you’ve found the queries that are taking a long time, you can use the EXPLAIN command (or the MySQL Query Browser graphical version) to see which parts of the query are taking the longest. It gives a lot of information about the query itself, but besides the times themselves the most useful information is related to the indexes. You can see how many records each portion has to crunch through, which indexes are available, and which indexes are being used. This leads to tip #3.

Index, Index, and….oh, Index.
Proper indexing is probably your best friend when it comes to increasing query performance. This has limited benefits for extremely dynamic databases with a high proportion of INSERTs and UPDATEs because the indexes will have to be rebuilt each time the table is changed, but where SELECTs are the bulk of the queries (most databases) indexing can contribute to huge increases in speed

More Info
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html
http://jpipes.com/presentations/mysql_perf_tuning.pdf [pdf warning]
http://20bits.com/2007/04/10/10-tips-for-optimizing-mysql-queries-that-dont-suck/
http://immike.net/blog/2007/04/09/how-not-to-optimize-a-mysql-query/