(mysql.info) multiple-column-indexes
Info Catalog
(mysql.info) indexes
(mysql.info) optimizing-database-structure
(mysql.info) mysql-indexes
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 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 mysql-indexes.
Info Catalog
(mysql.info) indexes
(mysql.info) optimizing-database-structure
(mysql.info) mysql-indexes
automatically generated byinfo2html