Previous Section  < Day Day Up >  Next Section

4.9 Creating and Dropping Indexes

Tables in MySQL can grow very large, but as a table gets bigger, retrievals from it become slower. To keep your queries performing efficiently, it's essential to index your tables. Indexes allow column values to be found more quickly, so retrievals based on indexes are faster than those that are not. Another reason to use indexes is that they can enforce uniqueness to ensure that duplicate values do not occur and that each row in a table can be distinguished from every other row.

MySQL supports four types of indexes:

  • A nonunique index is an index in which any key value may occur multiple times.

  • A UNIQUE index is unique-valued; that is, every key value is required to be different from all other keys.

  • A PRIMARY KEY is a unique-valued index that's similar to a UNIQUE index but has additional restrictions (the major one being that no NULL values are allowed).

  • A FULLTEXT index is specially designed for text searching.

This section discusses the following index-related topics:

  • 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

The discussion here does not consider in any depth indexing topics such as query optimization, assessing how well indexes are used, or FULLTEXT searching. The "Professional Study Guide" covers those topics in more detail.

4.9.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 keyword or keywords to indicate the index type, followed by a list in parentheses that names the column or columns that comprise the index. 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 nonunique 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.

For all index types other than PRIMARY KEY, you can name an index by including the name just before the column list. If you don't provide a name, MySQL assigns a name for you based on the name of the first column in the index. For a PRIMARY KEY, you don't specify a name because its name is always PRIMARY. (A consequence of this fact is that you cannot define more than one PRIMARY KEY per table.)

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 on 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 all non-NULL values.

A PRIMARY KEY is a type of index that's similar to a UNIQUE index. The differences between the two are as follows:

  • A UNIQUE index can contain NULL values; a PRIMARY KEY cannot. If a unique-valued index might be required to contain NULL values, you must use a UNIQUE index, not a PRIMARY KEY.

  • It's possible to have multiple UNIQUE indexes for a table, but 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.)

To index a column as a PRIMARY KEY, just 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.

4.9.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 restriction that requires indexed columns to contain only unique values.

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 of MySQL's index types can be used to implement the concept of a primary key:

  • An index created with a PRIMARY KEY clause

  • 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. 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.

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.

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.

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







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 option. 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 4.10, "Column Types."

4.9.3 Modifying Indexes of Existing Tables

To add an index to a table, you can use ALTER TABLE or CREATE INDEX. To drop an index from a table, you can use ALTER TABLE or DROP 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 would issue these statements:







ALTER TABLE HeadOfState ADD PRIMARY KEY (ID);

ALTER TABLE HeadOfState ADD INDEX (LastName,FirstName);

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. The preceding two ALTER TABLE statements can be combined as follows:







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

To drop an index with ALTER TABLE, use a DROP clause and name the index to be dropped. Dropping a PRIMARY KEY is easy:







ALTER TABLE HeadOfState DROP PRIMARY KEY;

To drop another kind of index, you must specify its name. If you don't know the name, you can use SHOW CREATE TABLE to see the table's structure, including any index definitions, as shown here:







mysql> SHOW CREATE TABLE HeadOfState\G

*************************** 1. row ***************************

       Table: HeadOfState

Create Table: CREATE TABLE `HeadOfState` (

  `ID` int(11) NOT NULL default '0',

  `LastName` char(30) NOT NULL default '',

  `FirstName` char(30) NOT NULL default '',

  `CountryCode` char(3) NOT NULL default '',

  `Inauguration` date NOT NULL default '0000-00-00',

  PRIMARY KEY  (`ID`),

  KEY `LastName` (`LastName`,`FirstName`)

) TYPE=MyISAM

The KEY clause of the output shows that the index name is LastName, so you can drop the index using the following statement:







ALTER TABLE HeadOfState DROP INDEX LastName;

After you've dropped an index, you can recover it merely by re-creating it. This differs from dropping a database or a table, which cannot be undone except by recourse to backups. The distinction is that when you drop a database or a table, you're removing data. When you drop an index, you aren't removing table data, you're merely removing a structure that's derived from the data. The act of removing an index is a reversible operation as long as the columns from which the index was constructed have not been removed.

CREATE INDEX and DROP INDEX provide alternatives to ALTER TABLE for index manipulation.

The syntax for CREATE INDEX is as follows, where the statements shown create a single-column UNIQUE index and a multiple-column nonunique 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, whereas ALTER TABLE creates an index name automatically if you don't provide one.

To drop an index with DROP INDEX, indicate the index name and table name:







DROP INDEX IDIndex ON t;

DROP INDEX NameIndex ON t;

Unlike ALTER TABLE, the CREATE INDEX and DROP INDEX statements can operate only on a single index per statement. In addition, neither statement supports the use of PRIMARY KEY. This is the reason that ALTER TABLE is more flexible.

    Previous Section  < Day Day Up >  Next Section