Skip navigation links
7.4.4 How MySQL Uses Indexes »
Section Navigation      [Toggle]

7.4.3. Multiple-Column Indexes

MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 7.4.2, “Column Indexes”).

A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns.

MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns.

Suppose that a table has the following specification: 

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index is used in the following queries: 

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

However, the name index is not used in the following queries: 

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

The manner in which MySQL uses indexes to improve query performance is discussed further in Section 7.4.4, “How MySQL Uses Indexes”.


User Comments

Posted by [name withheld] on December 29 2005 9:08pm[Delete] [Edit]

Note that the combined length of the index parts may not exceed 1000 bytes (this may be equal to the combined length of the columns that form the index, but only in
case you don't use subparts of the columns).

Posted by rajeev rai on April 22 2007 5:26pm[Delete] [Edit]

its important to note in the above example that the table will use the index on last_name because indexes are the sorted array of the key values.and since, last_name is given precedence in the table index name, index will work only on last_name and not on first_name.

Posted by Rob Wultsch on October 20 2008 7:04pm[Delete] [Edit]

It should be worth note that it is more ideal to have an OR affecting the left most column.

mysql> EXPLAIN SELECT * FROM test
-> WHERE last_name='Widenius'
-> AND (first_name='Michael' OR first_name='Monty');

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | name          | name | 90      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test
-> WHERE first_name='Widenius'
-> AND (last_name='Michael' OR last_name='Monty');
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | name          | name | 180     | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

(Note key_len in the examples above)

Posted by antonio cherry on June 12 2009 2:51pm[Delete] [Edit]

Note that when you create an index on multiple columns the show index query will return as many indices as the columns. So, for a primary key on two columns the table will show two "primary keys".

Add your own comment.