(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