(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