Speed up MySQL on development boxes

25 Apr 2007

I use my MacBook Pro for development and mainly use the open source database server MySQL. There are binary install packages for most Mac OS X systems, including Intel optimized ones and so it is very easy to setup. For maintenance and monitoring, I recommend installing the MySQL GUI Tools.

Always looking to speed things up, I read a bit on the query cache in MySQL 5 to improve the database performance. It turns out the cache is disabled by default. To enable the query cache, write the following in /etc/my.cnf:

[mysqld]
#The memory allocated to store results from old queries.
query_cache_size=1024k

Or, alternatively, set the value with MySQL Administrator under Options, Performance. I am - obviously - seeing noticable speed improvements with the cache enabled, particularly since web development tends to involve numerous refreshes of the same page, i.e. the same queries.

The cache shouldn’t go alone. You should always make sure you use indexes on the fields used in SELECTs and normalize the tables (There are occasions where normalization will slow down queries due to expensive JOINs, see Cal Henderson’s flickr talk for instance).

MySQL related, here’s a quick explanation of JOINs by Ligaya Turmelle.