8.6. Indexes | MySQL 5.0 Certification Study Guide

Tables in MySQL can grow very large, but as a table gets bigger, retrievals from it become slower. To keep your queries performing well, it's essential to index your tables. Indexes allow column values to be found more efficiently, so retrievals based on indexes are faster than those that are not. For large tables, the presence of an index can make the difference between a query that executes quickly and one that is unacceptably slow.

Another reason to use indexes is that they can enforce uniqueness constraints to ensure that duplicate values do not occur and that each row in a table can be distinguished from every other row.

This section discusses the following index-related topics:

Types of indexes
Defining indexes at table creation time with CREATE TABLE
Using primary keys
Adding indexes to existing tables with ALTER TABLE or CREATE INDEX
Dropping indexes from tables with ALTER TABLE or DROP INDEX
Choosing an indexing algorithm

8.6.1. Types of Indexes

MySQL supports three general types of indexes:

A primary key is an index for which each index value differs from every other and uniquely identifies a single row in the table. A primary key cannot contain NULL values.
A unique index is similar to a primary key, except that it can be allowed to contain NULL values. Each non-NULL value uniquely identifies a single row in the table.
A non-unique index is an index in which any key value may occur multiple times.

There are also more specialized types of indexes:

A FULLTEXT index is specially designed for text searching.
A SPATIAL index applies only to columns that have spatial data types.

FULLTEXT indexes are covered in Section 38.3.3, "FULLTEXT Indexes." SPATIAL indexes are not covered in this study guide or on the exam.

8.6.2. Creating Indexes

You can create indexes at the same time that you create a table by including index definitions in the CREATE TABLE along with the column definitions. It is also possible to add indexes to an existing table with ALTER TABLE or CREATE INDEX.

8.6.2.1 Defining Indexes at Table Creation Time

To define indexes for a table at the time you create it, include the index definitions in the CREATE TABLE statement along with the column definitions. An index definition consists of the appropriate index-type keyword or keywords, followed by a list in parentheses that names the column or columns to be indexed. Suppose that the definition of a table HeadOfState without any indexes looks like this:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL );

To create the table with the same columns but with a non-unique index on the date-valued column Inauguration, include an INDEX clause in the CREATE TABLE statement as follows:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL,     INDEX (Inauguration) );

The keyword KEY may be used instead of INDEX.

To include multiple columns in an index (that is, to create a composite index), list all the column names within the parentheses, separated by commas. For example, a composite index that includes both the LastName and FirstName columns can be defined as follows:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL,     INDEX (LastName, FirstName) );

Composite indexes can be created for any type of index.

The preceding indexing examples each include just one index in the table definition, but a table can have multiple indexes. The following table definition includes two indexes:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL,     INDEX (LastName, FirstName),     INDEX (Inauguration) );

To create a unique-valued index, use the UNIQUE keyword instead of INDEX. For example, if you want to prevent duplicate values in the ID column, create a UNIQUE index for it like this:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL,     UNIQUE (ID) );

There's one exception to the uniqueness of values in a UNIQUE index: If a column in the index may contain NULL values, multiple NULL values are allowed. This differs from the behavior for non-NULL values.

A PRIMARY KEY is similar to a UNIQUE index. The differences between the two are as follows:

A PRIMARY KEY cannot contain NULL values; a UNIQUE index can. If a unique-valued index must be allowed to contain NULL values, you must use a UNIQUE index, not a PRIMARY KEY.
Each table may have only one index defined as a PRIMARY KEY. (The internal name for a PRIMARY KEY is always PRIMARY, and there can be only one index with a given name.) It's possible to have multiple UNIQUE indexes for a table.

It follows from the preceding description that a PRIMARY KEY is a type of unique-valued index, but a UNIQUE index isn't necessarily a primary key unless it disallows NULL values. If it does, a UNIQUE index that cannot contain NULL is functionally equivalent to a PRIMARY KEY.

To index a column as a PRIMARY KEY, use the keywords PRIMARY KEY rather than UNIQUE and declare the column NOT NULL to make sure that it cannot contain NULL values.

The use of PRIMARY KEY and UNIQUE to create indexes that ensure unique identification for any row in a table is discussed in the next section.

8.6.2.2 Creating and Using Primary Keys

The most common reason for creating an index is that it decreases lookup time for operations that search the indexed columns, especially for large tables. Another important use for indexing is to create a constraint that requires each index value to be unique.

An index with unique values allows you to identify each record in a table as distinct from any other. This kind of index provides a primary key for a table. Without a primary key, there might be no way to identify a record that does not also identify other records at the same time. That is a problem when you need to retrieve, update, or delete a specific record in a table. A unique ID number is a common type of primary key.

Two kinds of indexes can be used to implement the concept of a primary key:

An index created with the PRIMARY KEY keywords
An index created with the UNIQUE keyword

In both cases, the column or columns in the index should be declared as NOT NULL. For a PRIMARY KEY, this is a requirement; MySQL won't create a PRIMARY KEY from any column that may be NULL. (If you omit NOT NULL from the definition of any PRIMARY KEY column, MySQL adds it implicitly to enforce the NOT NULL requirement.) For a UNIQUE index, declaring columns as NOT NULL is a logical requirement if the index is to serve as a primary key. If a UNIQUE index is allowed to contain NULL values, it may contain multiple NULL values. As a result, some rows might not be distinguishable from others and the index cannot be used as a primary key.

The following definition creates a table t that contains an id column that's NOT NULL and declared as a primary key by means of a PRIMARY KEY clause:

 CREATE TABLE t (     id   INT NOT NULL,     name CHAR(30) NOT NULL,     PRIMARY KEY (id) );

A primary key on a column also can be created by replacing PRIMARY KEY with UNIQUE in the table definition, provided that the column is declared NOT NULL:

 CREATE TABLE t (     id   INT NOT NULL,     name CHAR(30) NOT NULL,     UNIQUE (id) );

An alternative syntax is allowed for the preceding two statements. For a single-column primary key, you can add the keywords PRIMARY KEY or UNIQUE directly to the end of the column definition. The following CREATE TABLE statements are equivalent to those just shown:

 CREATE TABLE t (     id   INT NOT NULL PRIMARY KEY,     name CHAR(30) NOT NULL ); CREATE TABLE t (     id   INT NOT NULL UNIQUE,     name CHAR(30) NOT NULL );

Like other indexes, you can declare a PRIMARY KEY or UNIQUE index as a composite index that spans multiple columns. In this case, the index must be declared using a separate clause. (You cannot add the PRIMARY KEY or UNIQUE keywords to the end of a column definition because the index would apply only to that column.) The following definition creates a primary key on the last_name and first_name columns using a PRIMARY KEY clause:

 CREATE TABLE people (     last_name  CHAR(30) NOT NULL,     first_name CHAR(30) NOT NULL,     PRIMARY KEY (last_name, first_name) );

This primary key definition allows any given last name or first name to appear multiple times in the table, but no combination of last and first name can occur more than once.

If the columns are declared NOT NULL, you can also create a multiple-column primary key using UNIQUE:

 CREATE TABLE people (     last_name  CHAR(30) NOT NULL,     first_name CHAR(30) NOT NULL,     UNIQUE (last_name, first_name) );

Primary keys are an important general database design concept because they allow unique identification of each row in a table. For MySQL in particular, primary keys are frequently defined as columns that are declared with the AUTO_INCREMENT attribute. AUTO_INCREMENT columns provide a convenient way to automatically generate a unique sequence number for each row in a table and are described in Section 5.7, "Using the AUTO_INCREMENT Column Attribute."

8.6.2.3 Naming Indexes

For all index types other than PRIMARY KEY, you can name an index by including the name just before the column list. For example, the following definition uses names of NameIndex and IDIndex for the two indexes in the table:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL,     INDEX NameIndex (LastName, FirstName),     UNIQUE IDIndex (ID) );

If you don't provide a name for an index, MySQL assigns a name for you based on the name of the first column in the index.

For a PRIMARY KEY, you provide no name because the name is always PRIMARY. A consequence of this fact is that you cannot define more than one PRIMARY KEY per table because indexes, like columns, must have unique names.

Index names are displayed by the SHOW CREATE TABLE or SHOW INDEX statement.

8.6.2.4 Adding Indexes to Existing Tables

To add an index to a table, you can use ALTER TABLE or CREATE INDEX. Of these statements, ALTER TABLE is the most flexible, as will become clear in the following discussion.

To add an index to a table with ALTER TABLE, use ADD followed by the appropriate index-type keywords and a parenthesized list naming the columns to be indexed. For example, assume that the HeadOfState table used earlier in this chapter is defined without indexes as follows:

 CREATE TABLE HeadOfState (     ID           INT NOT NULL,     LastName     CHAR(30) NOT NULL,     FirstName    CHAR(30) NOT NULL,     CountryCode  CHAR(3) NOT NULL,     Inauguration DATE NOT NULL );

To create a PRIMARY KEY on the ID column and a composite index on the LastName and FirstName columns, you could issue these statements:

 ALTER TABLE HeadOfState ADD PRIMARY KEY (ID); ALTER TABLE HeadOfState ADD INDEX (LastName,FirstName);

However, MySQL allows multiple actions to be performed with a single ALTER TABLE statement. One common use for multiple actions is to add several indexes to a table at the same time, which is more efficient than adding each one separately. Thus, the preceding two ALTER TABLE statements can be combined as follows:

 ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);

The syntax for CREATE INDEX is as follows, where the statements shown create a single- column UNIQUE index and a multiple-column non-unique index, respectively:

 CREATE UNIQUE INDEX IDIndex ON HeadOfState (ID); CREATE INDEX NameIndex ON HeadOfState (LastName,FirstName);

Note that with CREATE INDEX, it's necessary to provide a name for the index. With ALTER TABLE, MySQL creates an index name automatically if you don't provide one.

Unlike ALTER TABLE, the CREATE INDEX statement can create only a single index per statement. In addition, only ALTER TABLE supports the use of PRIMARY KEY. For these reasons, ALTER TABLE is more flexible.

8.6.3. Choosing an Indexing Algorithm

When you create an index, it is possible to specify the indexing algorithm to be used. The only engine for which this feature is currently applicable is the MEMORY engine that manages in-memory tables. For other engines, the syntax is recognized but ignored.

MEMORY tables use hash indexes by default. This index algorithm provides very fast lookups for all operations that use a unique index. However, hash indexes are usable only for comparisons that use the = or <=> operator. Also, for non-unique indexes, operations that change the indexed values (including DELETE statements) can become relatively slow when there are many duplicate index values.

If you will have only unique indexes on a MEMORY table, you should create them as HASH indexes. Because HASH indexes are the default for MEMORY tables, you can do so when defining an index either by specifying an explicit USING HASH clause or by omitting the index algorithm specification entirely. The following two statements are equivalent:

 CREATE TABLE lookup (     id INT,     INDEX USING HASH (id) ) ENGINE = MEMORY; CREATE TABLE lookup (     id INT,     INDEX (id) ) ENGINE = MEMORY;

On the other hand, if a MEMORY table contains only non-unique indexes for which you expect that there will be many duplicate values in the index key, a BTREE index is preferable. BTREE indexes also are usable if the indexed column will be used with comparison operators other than = or <=>. For example, BTREE can be used for range searches such as id < 100 or id BETWEEN 200 AND 300. To create an index that uses the BTREE algorithm, include a USING BTREE clause in the index definition:

 CREATE TABLE lookup (   id INT,    INDEX USING BTREE (id) ) ENGINE = MEMORY;

If you have already created the table, you can add a new index using either ALTER TABLE or CREATE INDEX, making use of the USING index_type clause. If the lookup table had been created without the index on the id column, either of the following statements would add a BTREE index on that column:

 ALTER TABLE lookup ADD INDEX USING BTREE (id); CREATE INDEX id_idx USING BTREE ON lookup (id);

Although choosing between alternative indexing algorithms currently is limited to MEMORY tables, work is ongoing on extending this functionality to other storage engines such as MyISAM and InnoDB.