Optimising your Data

Week 4 refered us to the massive and extensive MySQL Reference Manual. In particular, we were drawn to Chapter 12: Getting Maximum Performance from MySQL.

A small and very useful section entitled Get Your Data as Small as Possible (section 12.3), listed various techniques for optimising your tables and minimsing storage space.

Optimisation Recommendations

Use the most efficient (smallest) types possible. MySQL has many specialised types that save disk space and memory.

Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is ofthen better than INT.

Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use it. Just avoid it on all columns by default.

If you dont have any variable length column (VARCHAR, TEXT or BLOB columns), a fixed size record format is used. This is faster but unfortunately may waste some space.

The primary key index of a table should be as short as possible. This makes identification of one row easy and efficient.

Only create the indexes that you really need. Indexes are good for retrieval but bad when you need to store things fast. If you mostly access a table by searching on a combination of columns, make an index on them. The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index.

If it's very likely that a column has a unique prefix on the first number of characters, its better ti only index this prefix, MySQL supports an index on a part of a character column. Shorter indexes are faster not only because they take less disk space but also because they will give you more hits in the index cache and thus fewer disk seeks.

In some circumstances it can be beneficial to split into two tables that is scanned very often, This is especially true if it is a dynamic format table and it is possible to use a smaller staic format table that can be used to find the relevant rows when scanning the table.

MySQL Reference Manual v3.23.33 (2001)

My Conclusion

I guess that the above recommendations will become easier to implement the more you use MySQL. Like they say practice makes perfect, and what better way to learn.

All the best Jayson