7.4.4. Multiple-Column Indexes
MySQL can create composite indexes (that is, indexes on multiple
columns). An index may consist of up to 15 columns. For certain
data types, you can index a prefix of the column (see
Section 7.4.3, “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.5, “How MySQL Uses Indexes”.
