(mysql.info.gz) Multiple-column indexes
Info Catalog
(mysql.info.gz) Indexes
(mysql.info.gz) Optimizing Database Structure
(mysql.info.gz) MySQL indexes
7.4.4 Multiple-Column Indexes
-----------------------------
MySQL can create indexes on multiple columns. An index may consist of
up to 15 columns. For certain column types, you can index a prefix of
the 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 don't 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 `last_name' and `first_name'. 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 will be 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 will _not_ be 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 the next section.
Info Catalog
(mysql.info.gz) Indexes
(mysql.info.gz) Optimizing Database Structure
(mysql.info.gz) MySQL indexes
automatically generated byinfo2html