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. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.
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 at the end of
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.
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.
Comparison of dissimilar columns may prevent use of indexes
if values cannot be compared directly without conversion.
Suppose that a numeric column is compared to a string
column. For a given value such as
the numeric column, it might compare equal to any number of
values in the string column such as
'01.e1'. 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 key (for example,
ORDER BY ). If all key
parts are followed by
DESC, the key is
read in reverse order. See
Section 126.96.36.199, “
ORDER BY Optimization”, and
Section 188.8.131.52, “
GROUP BY Optimization”.
In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed:
Suppose that you issue the following
SELECT * FROM
If a multiple-column index exists on
col2, the appropriate rows can be fetched
directly. If separate single-column indexes exist on
optimizer will attempt to use the Index Merge optimization (see
Section 184.108.40.206, “Index Merge Optimization”), or attempt to find
the most restrictive index by deciding which index finds fewer
rows and using that index to fetch the rows.
If the table has a multiple-column index, any leftmost prefix of
the index can be used by the optimizer to find rows. For
example, if you have a three-column index on
col2, col3), you have indexed search capabilities on
(col1, col2), and
(col1, col2, col3).
MySQL cannot use an index if the columns do not form a leftmost
prefix of the index. Suppose that you have the
SELECT statements shown here:
SELECT * FROM
val1; SELECT * FROM
val2; SELECT * FROM
val2; SELECT * FROM
If an index exists on
(col1, col2, col3),
only the first two queries use the index. The third and fourth
queries do involve indexed columns, but
are not leftmost prefixes of
A B-tree index can be used for column comparisons in expressions
that use the
BETWEEN operators. The index
also can be used for
comparisons if the argument to
is a constant string that does not start with a wildcard
character. For example, the following
SELECT statements use indexes:
SELECT * FROM
key_colLIKE 'Patrick%'; SELECT * FROM
In the first statement, only rows with
considered. In the second statement, only rows with
key_col < 'Patricl'
'Pat' <= are considered.
do not use indexes:
SELECT * FROM
key_colLIKE '%Patrick%'; SELECT * FROM
If you use
string is longer than three
characters, MySQL uses the Turbo Boyer-Moore
algorithm to initialize the pattern for the string
and then uses this pattern to perform the search more quickly.
A search using
employs indexes if
col_name is indexed.
WHERE clauses use indexes:
index= 1 OR
index= 2 */ ... WHERE
index=1 OR A=10 AND
index=2 /* optimized like "
index_part1='hello'" */ ... WHERE
index_part3=5 /* Can use index on
index1but not on
index3*/ ... WHERE
WHERE clauses do
not use indexes:
index_part1is not used */ ... WHERE
index_part3=2 /* Index is not used in both parts of the WHERE clause */ ... WHERE
index=1 OR A=10 /* No index spans all rows */ ... WHERE
Sometimes MySQL does not use an index, even if one is available.
One circumstance under which this occurs is when the optimizer
estimates that using the index would require MySQL to access a
very large percentage of the rows in the table. (In this case, a
table scan is likely to be much faster because it requires fewer
seeks.) However, if such a query uses
to retrieve only some of the rows, MySQL uses an index anyway,
because it can much more quickly find the few rows to return in
Hash indexes have somewhat different characteristics from those just discussed:
They are used only for equality comparisons that use the
operators (but are very fast). They are
not used for comparison operators such as
< that find a range of values.
The optimizer cannot use a hash index to speed up
ORDER BY operations. (This type of index
cannot be used to search for the next entry in order.)
MySQL cannot determine approximately how many rows there are
between two values (this is used by the range optimizer to
decide which index to use). This may affect some queries if
you change a
MyISAM table to a
Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)