Some general tips for speeding up queries on
To help MySQL better optimize queries, use
ANALYZE TABLE or run
myisamchk --analyze on a table after it
has been loaded with data. This updates a value for each
index part that indicates the average number of rows that
have the same value. (For unique indexes, this is always 1.)
MySQL uses this to decide which index to choose when you
join two tables based on a nonconstant expression. You can
check the result from the table analysis by using
SHOW INDEX FROM
Cardinality value. myisamchk
--description --verbose shows index distribution
To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.
Try to avoid complex
MyISAM tables that are updated
frequently, to avoid problems with table locking that occur
due to contention between readers and writers.
MyISAM supports concurrent inserts: If a
table has no free blocks in the middle of the data file, you
INSERT new rows into it
at the same time that other threads are reading from the
table. If it is important to be able to do this, consider
using the table in ways that avoid deleting rows. Another
possibility is to run
TABLE to defragment the table after you have
deleted a lot of rows from it. This behavior is altered by
You can force new rows to be appended (and therefore permit
concurrent inserts), even in tables that have deleted rows.
See Section 8.10.3, “Concurrent Inserts”.
MyISAM tables that change frequently,
try to avoid all variable-length columns
TEXT). The table uses dynamic
row format if it includes even a single variable-length
column. See Chapter 14, Storage Engines.
It is normally not useful to split a table into different
tables just because the rows become large. In accessing a
row, the biggest performance hit is the disk seek needed to
find the first byte of the row. After finding the data, most
modern disks can read the entire row fast enough for most
applications. The only cases where splitting up a table
makes an appreciable difference is if it is a
MyISAM table using dynamic row format
that you can change to a fixed row size, or if you very
often need to scan the table but do not need most of the
columns. See Chapter 14, Storage Engines.
ALTER TABLE ... ORDER BY
usually retrieve rows in
using this option after extensive changes to the table, you
may be able to get higher performance.
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 very important when you use MySQL storage engines
MyISAM that has only table-level
locking (multiple readers with single writers). This also
gives better performance with most database systems, because
the row locking manager in this case has less to do.
INSERT DELAYED when you
do not need to know when your data is written. This reduces
the overall insertion impact because many rows can be
written with a single disk write.
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.
HIGH_PRIORITY have an effect only for
storage engines that use only table-level locking (such as
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 must ensure
that the table is okay by running the server with the
option, or by running myisamchk before
restarting the server. (However, even in this case, you
should not lose anything by using
DELAY_KEY_WRITE, because the key
information can always be generated from the data rows.)
Strings are automatically prefix- and end-space compressed
MyISAM indexes. See
Section 13.1.13, “
CREATE INDEX Syntax”.
You can increase performance by caching queries or answers in your application and then executing many inserts or updates together. Locking the table during this operation ensures 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 Section 8.9.3, “The MySQL Query Cache”.