7.4.2. Make Your Data as Small as Possible
One of the most basic optimizations is to design your tables to
take as little space on the disk as possible. This can result in
huge improvements because disk reads are faster, and smaller
tables normally require less main memory while their contents
are being actively processed during query execution. Indexing
also is a lesser resource burden if done on smaller columns.
MySQL supports many different storage engines (table types) and
row formats. For each table, you can decide which storage and
indexing method to use. Choosing the proper table format for
your application may give you a big performance gain. See
Chapter 14, Storage Engines and Table Types.
You can get better performance for a table and minimize storage
space by using the techniques listed here:
Use the most efficient (smallest) data types possible. MySQL
has many specialized types that save disk space and memory.
For example, use the smaller integer types if possible to
get smaller tables.
MEDIUMINT is often a
better choice than
INT because a
MEDIUMINT column uses 25% less space.
Declare columns to be
NOT NULL if
possible. It makes everything faster and you save one bit
per column. If you really need
your application, you should definitely use it. Just avoid
having it on all columns by default.
MyISAM tables, if you do not have any
variable-length columns (
columns), a fixed-size row 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
InnoDB tables use a compact storage
format. In versions of MySQL earlier than 5.0.3,
InnoDB rows 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, you can request the
old format with
InnoDB format also changes
CHAR columns containing UTF-8 data
are stored. With
occupies 3 ×
N bytes, given
that the maximum length of a UTF-8 encoded character is
three bytes. Many languages can be written primarily using
single-byte UTF-8 characters, so a fixed storage length
often wastes space. With
InnoDB allocates a variable amount of
storage in the range from
N to 3
N bytes for these columns
by stripping trailing spaces if necessary. The minimum
storage length is kept as
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 data
quickly. If you access a table mostly by searching on a
combination of columns, create an index on them. The first
part of the index should be the column most used. If you
always use many columns when selecting
from the table, you should use the column with more
duplicates first to obtain better compression of the index.
If it is very likely that a string column has a unique
prefix on the first number of characters, it's better to
index only this prefix, using MySQL's support for creating
an index on the leftmost part of the column (see
Section 13.1.4, “
CREATE INDEX Syntax”). Shorter indexes are faster,
not only because they require less disk space, but because
they give also 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.