How MySQL Uses Indexes

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 (PRIMARY KEY, UNIQUE, INDEX, and 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.4, “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 this section.

数据挖掘研究院

MySQL uses indexes for these operations:

Suppose that you issue the following SELECT statement: 数据挖掘研究院

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
 
数据挖掘研究院
 

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries 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 (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). 数据挖掘研究院

MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here: 数据挖掘研究院

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
 
数据挖掘研究院
 

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 (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

数据挖掘研究院

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes: 数据挖掘研究院

SELECT * FROM tbl_name WHERE key_col LIKE ′Patrick%′;
SELECT * FROM tbl_name WHERE key_col LIKE ′Pat%_ck%′;
 数据挖掘研究院 

In the first statement, only rows with ′Patrick′ <= key_col < ′Patricl′ are considered. In the second statement, only rows with ′Pat′ <= key_col < ′Pau′ are considered.

数据挖掘研究院

The following SELECT statements do not use indexes: 数据挖掘实验室

SELECT * FROM tbl_name WHERE key_col LIKE ′%Patrick%′;
SELECT * FROM tbl_name WHERE key_col LIKE other_col;
 
数据挖掘研究院
 

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

If you use ... LIKE ′%string%′ and 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 col_name IS NULL employs indexes if col_name is indexed. 数据挖掘实验室

Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.

数据挖掘研究院

The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1=′hello′" */
... WHERE index_part1=′hello′ AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
 
数据挖掘研究院
 

These WHERE clauses do not use indexes:

    /* index_part1 is not used */
... WHERE index_part2=1 AND 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 index_part1=1 OR index_part2=10
 
数据挖掘研究院
 

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 LIMIT 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 the result.

数据挖掘研究院

Hash indexes have somewhat different characteristics from those just discussed: 数据挖掘实验室

| table | type  | possible_keys | key   | key_len | ref  
| t     | index | t_idx         | t_idx |      15 | NULL
 
数据挖掘研究院
 

| table | type | possible_keys | key  | key_len | ref
| t     | ALL  | t_idx         | NULL |    NULL | NULL
 数据挖掘研究院