GNU Info

(mysql.info)Multiple-column indexes

---

Next: Table cache Prev: Indexes Up: Optimizing Database Structure

---

Enter node , (file) or (file)node

Multiple-Column Indexes
-----------------------

MySQL can create indexes on multiple columns.  An index may consist of
up to 15 columns. (On `CHAR' and `VARCHAR' columns you can also use a
prefix of the column as a part of an index).

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 a table is created using the following specification:

     mysql> 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));

Then the index `name' is an index over `last_name' and `first_name'.
The index will 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:

     mysql> SELECT * FROM test WHERE last_name="Widenius";
     
     mysql> SELECT * FROM test WHERE last_name="Widenius"
                               AND first_name="Michael";
     
     mysql> SELECT * FROM test WHERE last_name="Widenius"
                               AND (first_name="Michael" OR first_name="Monty");
     
     mysql> 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:

     mysql> SELECT * FROM test WHERE first_name="Michael";
     
     mysql> SELECT * FROM test WHERE last_name="Widenius"
                               OR first_name="Michael";

For more information on the manner in which MySQL uses indexes to
improve query performance, see Note: MySQL indexes.