MySQL Reference Manual

Chapter 5. MySQL Optimisation

Optimisation is a complicated task because it ultimately requires understanding of the whole system. While it may be possible to do some local optimisations with little knowledge of your system or application, the more optimal you want your system to become the more you will have to know about it.

This chapter will try to explain and give some examples of different ways to optimise MySQL. Remember, however, that there are always some (increasingly harder) additional ways to make the system even faster.

Optimisation Overview

The most important part in speeding up a system is, of course, the basic design. You also need to know what your system will be doing, and what your bottlenecks are.

The most common bottlenecks are:

• Disk seeks. It takes time for the disk to find a piece of data. With modern disks in 1999, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimise for a single table. The way to optimise this is to spread the data on more than one disk.

• Disk reading/writing. When the disk is at the correct position we need to read the data. With modern disks in 1999, one disk delivers something like 10-20M/s. This is easier to optimise than seeks because you can read in parallel from multiple disks.

• CPU cycles. When we have the data in main memory (or if it already was there) we need to process it to get to our result. Having small tables compared ...