MySQL Reference Manual :: 7.4.2 Make Your Data as Small as Possible

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

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

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

For MyISAM tables, if you don't have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size record format is used. This is faster but unfortunately may waste some space. See Section 14.1.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE option ROW_FORMAT=fixed.

Starting with MySQL/InnoDB 5.0.3, InnoDB tables use a more compact storage format. In earlier versions of MySQL, InnoDB records contain some redundant information, such as the number of columns and the length of each column, even for fixed-size columns. By default, tables are created in the compact format (ROW_FORMAT=COMPACT). If you wish to downgrade to older versions of MySQL/InnoDB, you can request the old format with ROW_FORMAT=REDUNDANT.

The compact InnoDB format also changes the way how CHAR columns containing UTF-8 data are stored. In the ROW_FORMAT=REDUNDANT format, a UTF-8 CHAR(n) occupies 3*n bytes, given that the maximum length of a UTF-8 encoded character is 3 bytes. Since many languages can be written mostly with single-byte UTF-8 characters, a fixed storage length often wastes space. The ROW_FORMAT=COMPACT format allocates a variable amount of n..3*n bytes for these columns by stripping trailing spaces if necessary. The minimum storage length is kept as n bytes in order to facilitate in-place updates in typical cases.

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

Create only 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, it's better to index only this prefix. MySQL supports an index on the leftmost part of a character column. Shorter indexes are faster not only because they take less disk space, but also because they give you more hits in the index cache and thus fewer disk seeks. See Section 7.5.2, “Tuning Server Parameters”.

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