This section lists a number of miscellaneous tips for improving query processing speed:
Use persistent connections to the database to avoid
connection overhead. If you can't use persistent connections
and you are initiating many new connections to the database,
you may want to change the value of the
thread_cache_size variable. See
Sección 7.5.2, “Afinar parámetros del servidor”.
Always check whether all your queries really use the indexes
you have created in the tables. In MySQL, you can do this
EXPLAIN statement. See
Sección 7.2.1, “Sintaxis de
EXPLAIN (Obtener información acerca de
Try to avoid complex
SELECT queries on
MyISAM tables that are updated
frequently, to avoid problems with table locking that occur
due to contention between readers and writers.
MyISAM tables that have no deleted
rows, you can insert rows at the end at the same time that
another query is reading from the table. If this is
important for you, you should consider using the table in
ways that avoid deleting rows. Another possibility is to run
OPTIMIZE TABLE after you have deleted a
lot of rows.
ALTER TABLE ... ORDER BY
mostly retrieve rows in
using this option after extensive changes to the table, you
may be able to get higher performance.
In some cases, it may make sense to introduce a column that is “hashed” based on information from other columns. If this column is short and reasonably unique, it may be much faster than a big index on many columns. In MySQL, it is very easy to use this extra column:
SELECT * FROM
MyISAM tables that change a lot, you
should try to avoid all variable-length columns
TEXT). The table uses dynamic record
format if it includes even a single variable-length column.
See Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.
It is normally not useful to split a table into different
tables just because rows become large. To access a row, the
biggest performance hit is the disk seek to find the first
byte of the row. After finding the data, most modern disks
can read the entire row quickly enough for most
applications. The only case in which it is advantageous to
split up a table is if the table is a
MyISAM table with dynamic record format
(see above) that you can change to a fixed record size, or
if you very often need to scan the table but do not need
most of the columns. See Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.
If you often need to calculate results such as counts based on information from a lot of rows, it may be preferable to introduce a new table and update the counter in real time. An update of the following form is very fast:
This is important when you use a MySQL storage engine such
MyISAM that has only table-level
locking (multiple readers / single writers). This gives
better performance with most databases, because the row
locking manager in this case has less to do.
If you need to collect statistics from large log tables, use summary tables instead of scanning the entire log table. Maintaining the summaries should be much faster than trying to calculate statistics “live”. Regenerating new summary tables from the logs when things change (depending on business decisions) is faster than changing the running application.
If possible, you should classify reports as “live” or as “statistical”, where data needed for statistical reports is created only from summary tables that are generated periodically from the live data.
When possible, take advantage columns' default values to insert values explicitly only when the value to be inserted differs from the default. This reduces the amount parsing required of MySQL and so improves the speed of insert operations.
In some cases, it is convenient to pack and store data into
BLOB column. In this case, you must
provide code in your application to pack and unpack
information, but this may save a lot of accesses at some
stage. This is practical when you have data that does not
conform well to a rows-and-columns table structure.
Normally, you should try to keep all data non-redundant (observing what is referred to in database theory as third normal form ). However, there may be situations in which it can be advantageous to duplicate information or create summary tables in order to gain more speed.
Stored procedures or UDFs (user-defined functions) may be a good way to gain performance for some tasks. See Capítulo 19, Procedimientos almacenados y funciones and Sección 27.2, “Añadir nuevas funciones a MySQL” for more information about these.
You can always gain something by caching queries or answers in your application and then performing many inserts or updates together. If your database supports table locks (like MySQL and Oracle), this should help to ensure that the index cache is only flushed once after all updates. You can also take advantage of MySQL's query cache to achieve similar results; see Sección 5.12, “La caché de consultas de MySQL”.
INSERT DELAYED when you do not need
to know when your data is written. This speeds things up
because many records can be written with a single disk
INSERT LOW_PRIORITY when you want to
SELECT statements higher priority
than your inserts.
SELECT HIGH_PRIORITY to get
retrievals that jump the queue. That is, the
SELECT is executed even if there is
another client waiting to do a write.
INSERT statements to
store many rows with one SQL statement (many SQL servers
support this, including MySQL).
LOAD DATA INFILE to load large
amounts of data. This is faster than using
AUTO_INCREMENT columns to generate
OPTIMIZE TABLE once in a while to
avoid fragmentation with
when using a dynamic table format. See
Sección 14.1.3, “Formatos de almacenamiento de tablas
MEMORY tables when possible to get
more speed. See Sección 14.3, “El motor de almacenamiento
With Web servers, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database. Most Web servers are better at caching files than database contents, so using files is generally faster.
Use in-memory tables for non-critical data that is accessed often, such as information about the last displayed banner for users who don't have cookies enabled in their Web browser. User sessions are another alternative available in many Web application environments for handling volatile state data.
Columns with identical information in different tables should be declared to have identical data types.
Try to keep column names simple. For example, in a table
customer, use a column name of
name instead of
customer_name. To make your names
portable to other SQL servers, you should keep them shorter
than 18 characters.
If you need really high speed, you should take a look at the
low-level interfaces for data storage that the different SQL
servers support. For example, by accessing the MySQL
MyISAM storage engine directly, you could
get a speed increase of two to five times compared to using
the SQL interface. To be able to do this, the data must be
on the same server as the application, and usually it should
only be accessed by one process (because external file
locking is really slow). One could eliminate these problems
by introducing low-level
in the MySQL server (this could be one easy way to get more
performance if needed). By carefully designing the database
interface, it should be quite easy to support this types of
If you are using numerical data, it is faster in many cases to access information from a database (using a live connection) than to access a text file. Information in the database is likely to be stored in a more compact format than in the text file, so accessing it involves fewer disk accesses. You also save code in your application because you don't have to parse your text files to find line and column boundaries.
Replication can provide performance benefits for some operations. You can distribute client retrievals among replication servers to split up the load. To avoid slowing down the master while making backups, you can make backups using a slave server. See Capítulo 6, Replicación en MySQL.
MyISAM table with the
DELAY_KEY_WRITE=1 table option makes
index updates faster because they are not flushed to disk
until the table is closed. The downside is that if something
kills the server while such a table is open, you should
ensure that they are okay by running the server with the
--myisam-recover option, or by running
myisamchk before restarting the server.
(However, even in this case, you should not lose anything by
DELAY_KEY_WRITE, because the key
information can always be generated from the data rows.)
É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.