Guest Author - Lisa Shea

If you are working with a MySQL database, it is important to optimize the database so it runs as quickly as possible. Here are some tips to help out with this task.

Disk Drive
Pretty much every MySQL query involves doing disk drive searches, i.e. roaming the disk drive looking at data. If your disk drive is busy doing other things - writing log files, handling other applications - this is going to be slow and involve a lot of jumping around. If you get a fast hard drive just for the MySQL data, then that hard drive's head can be very efficient at getting that data for you.

Queries
Naturally, if you write awful queries, they will take eons to run. Don't do a select against a giant table on a field that has no index. Definitely don't do "like" queries against large text fields! Tune your queries so that each one is as efficient as possible.

Server Memory Settings
There are a lot of settings in the my.ini file that you can edit. If you are running a relatively large database, here are four to look at:

table_cache=1250
key_buffer_size=64M
sort_buffer_size=4M
read_buffer_size=1M

The values there are ones I've used, but you can tune them based on the memory your system has. The table cache is the actual # of tables that can be open, while the other numbers are meg of memory available.

If you're running InnoDB for solid transactions - which I recommend - here are some settings to set -

innodb_buffer_pool_size=300M
innodb_log_file_size=100M