Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.
Most MySQL indexes (
FULLTEXT) are stored in B-trees. Exceptions
are that indexes on spatial data types use R-trees, and that
MEMORY tables also support hash indexes.
Strings are automatically prefix- and end-space compressed. See Section 13.1.13, “CREATE INDEX Syntax”.
In general, indexes are used as described in the following
discussion. Characteristics specific to hash indexes (as used in
MEMORY tables) are described in
Section 8.3.8, “Comparison of B-Tree and Hash Indexes”.
MySQL uses indexes for these operations:
To find the rows matching a
To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
If the table has a multiple-column index, any leftmost
prefix of the index can be used by the optimizer to look up
rows. For example, if you have a three-column index on
(col1, col2, col3), you have indexed
search capabilities on
(col1, col2), and
col3). For more information, see
Section 8.3.5, “Multiple-Column Indexes”.
To retrieve rows from other tables when performing joins.
MySQL can use indexes on columns more efficiently if they
are declared as the same type and size. In this context,
CHAR are considered the same
if they are declared as the same size. For example,
CHAR(10) are the same size, but
CHAR(15) are not.
For comparisons between nonbinary string columns, both
columns should use the same character set. For example,
utf8 column with a
latin1 column precludes use of an index.
Comparison of dissimilar columns (comparing a string column
to a temporal or numeric column, for example) may prevent
use of indexes if values cannot be compared directly without
conversion. For a given value such as
in the numeric column, it might compare equal to any number
of values in the string column such as
This rules out use of any indexes for the string column.
To find the
MAX() value for a specific
key_col. This is
optimized by a preprocessor that checks whether you are
WHERE on all key
parts that occur before
in the index. In this case, MySQL does a single key lookup
MAX() expression and replaces
it with a constant. If all expressions are replaced with
constants, the query returns at once. For example:
To sort or group a table if the sorting or grouping is done
on a leftmost prefix of a usable index (for example,
ORDER BY ). If all key
parts are followed by
DESC, the key is
read in reverse order. See
Section 22.214.171.124, “ORDER BY Optimization”, and
Section 126.96.36.199, “GROUP BY Optimization”.
In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses from a table only columns that are included in some index, the selected values can be be retrieved from the index tree for greater speed:
Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query. See Section 188.8.131.52, “How to Avoid Full Table Scans” for details.