Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  How MySQL Uses Indexes

8.3.1 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. This is much faster than reading every row sequentially.

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.

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 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).

  • 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), (col1, col2), and (col1, col2, 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, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

    For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a 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 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column.

  • To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:

    SELECT MIN(key_part2),MAX(key_part2)
      FROM tbl_name WHERE key_part1=10;
  • 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 key_part1, key_part2). 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 from a table only columns that are included in some index, the selected values can be retrieved from the index tree for greater speed:

    SELECT key_part3 FROM tbl_name
      WHERE key_part1=1

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, “How to Avoid Full Table Scans” for details.

Download this Manual
User Comments
  Posted by Donny Simonton on November 4, 2003
Instead of using OR, just use IN. For example, you would write your query like:

select MembershipNumber, MemberID from Member where MemberID IN ('123896920', '1029540');

I'm running 4.1 currently and the difference between an OR and IN are nothing. But on an older 3.x box we have IN makes a huge difference.

  Posted by Peter Brodersen on June 7, 2004
Selecting data using OR on two different columns with individual indices could be optimized using UNION (thereby creating two separate queries). UNION was introduced in MySQL 4.0.

From the above example: (notice that two different fields are used - MemberID and MembershipNumber - if the field was the same, IN() or OR would be optimized)

select MembershipNumber, MemberID from Member where MemberID = 123896920 or MembershipNumber = 1029540;

select MembershipNumber, MemberID from Member where MemberID = 123896920
select MembershipNumber, MemberID from Member where MembershipNumber = 1029540;

Two fast selects will be performed where indices are used. UNION will remove a possible duplicate (and UNION ALL would leave the duplicate).

Sign Up Login You must be logged in to post a comment.