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.
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 this section.
MySQL uses indexes for these operations:
To find the rows matching a
WHERE clause quickly.
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 (the most selective index).
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.
CHAR(10) are the
same size, but
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
1 in the numeric column, it might compare equal to any number
of values in the string column such as
rules out use of any indexes for the string column.
To find the
MAX() value for a specific indexed column
This is optimized by a preprocessor that checks whether you are using
on all key parts that occur before
key_col in the index. In
this case, MySQL does a single key lookup for each
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
ORDER BY Optimization", and Section
GROUP BY Optimization".
In some cases, a query can be optimized to retrieve values without consulting the data rows. (An index that provides all the necessary results for a query is called a covering index.) If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values can 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 18.104.22.168, "How to Avoid Full Table Scans" for details.