One of the most basic optimizations is to design your tables to take as little space on the disk as possible. This can give 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 a lot of different table types and row formats. For each table, you can decide which storage/index method to use. Choosing the right table format for your application may give you a big performance gain. See Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.
You can get better performance on a table and minimize storage space using the techniques listed here:
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 a
better choice than
INT since 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 record format is used. This is faster
but unfortunately may waste some space. See
Sección 14.1.3, “Formatos de almacenamiento de tablas
MyISAM”. You can hint that
you want to have fixed length rows even if you have
VARCHAR columns with the
Starting with MySQL/InnoDB 5.0.3,
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
The compact InnoDB format also changes the way how
CHAR columns containing UTF-8 data are
stored. In the
format, a UTF-8
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.
ROW_FORMAT=COMPACT format allocates a
variable amount of
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 data quickly. If you access a table mostly by searching on a combination of columns, make an index on them. The first index part should be the most used column. 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 column has a unique prefix on the first number of characters, it is 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 Sección 7.5.2, “Afinar parámetros del servidor”.
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.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a firstname.lastname@example.org.