How to Optimize Your MySQL Data Files

The first method to optimize your database is to make the tables for it as small as you possibly can. If you manage to do this, disk reads are faster, and smaller tables normally require less main memory while their contents are being actively processed during query execution. If the columns are also very small the indexing will work smoothly, without using many resources. Another way to optimize your database is to choose the table format that will give you a big performance boost.

Declaring columns to be NOT NULL will save one bit per column. This doesn't mean that you can use NULL in your application. If you need it, use it; just avoid to use it in every column. Another way to optimize tables minimizing the disk space used is to employ the smallest data types possible. For example, MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

Also, make sure the primary index of the table is as short as possible. This will make the identification of the rows more efficient. Creating indexes only if you need it is also a good solution for optimization. If you use many indexes, that will affect the data storing speed. To obtain better compression of the index, use first the column with more duplicates. You should remember that shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache and fewer disk seeks.

If you use MyISAM tables you should apply a fixed-size row format only if you don't have any variable-length columns. This method is faster, but unfortunately, it may waste some space. Using CREATE TABLE option ROW_FORMAT=FIXED you can hint that you want to have fixed length rows even if you have VARCHAR columns.

Because MySQL uses so many storage engines and row formats, make sure you use the proper table format and indexing method.