Managing Indexes in MySQL

MySQL supports four kinds of indexes:

• PRIMARY KEY

• UNIQUE

• INDEX

• FULLTEXT

The first two allow only unique values to be used in the column to which they apply. Only one unique index can be designated the PRIMARY KEY, but several indexes can be designated UNIQUE. The third kind permits the reuse of the same value in that column and is denoted by the keyword INDEX.

The fourth index type, FULLTEXT, is intended for speeding up full-text searches within VARCHAR or TEXT type columns. It works with the MATCH function of a WHERE clause, as was explained in Day 8, “Querying Data.” FULLTEXT was added in MySQL version 3.23.23.

As you will see in a moment, you can apply an index on a single column of a table, or on multiple columns. With a text column, you can create an index based on the first few characters of the values in that column (known as a partial index).

You can have several indexes on the same table. The MyISAM table type permits 32 indexes on a table by default, and no MySQL table type restricts you to fewer than 16 indexes.

Syntax for Adding and Dropping Indexes

You can add indexes at table creation time using CREATE TABLE, or later using ALTER TABLE. You can also drop an index from a table. Let's look at the syntax for doing these things.

Adding Indexes During Table Creation

It's common to create an index at table creation time. Let's recall the syntax for creating a table (simplified a little):

CREATE TABLE table_name (create_definition,...)

As well as declaring the columns, to define an index (or indexes), you would have at least one create_definition with syntax like one of these:

PRIMARY KEY (column_list)

UNIQUE [index_name] (column_list)

INDEX|KEY [index_name] (column_list)

FULLTEXT [index_name] (column_list)

where column_list is the name of a single column to be indexed, or for a multiple-column index, several column names separated by commas.

You can optionally specify index_name, the name of the index. If you do not, the name of the column being indexed will be used, or the first column if there are multiple columns being indexed. (If you want to drop an index, you will need to refer to it by its index name.)

For a non-unique index, you can use the keywords INDEX and KEY equally.

If you are adding several indexes to the table at once, remember to put a comma after the create_definition for each index, unless it's the last thing in the list for CREATE TABLE.

If you're defining a primary key on a column, that column will not be able to contain any null values, and the column must be declared NOT NULL. In MySQL version 3.23.2 and later and with the MyISAM table type, you can have null values in unique and non-unique keys (that is, UNIQUE and INDEX) but not in a PRIMARY KEY. However, in other versions and with other table types, null values are not allowed, and the definitions of the relevant columns must contain NOT NULL.

For example, let's create a table called products, with one key of each type for illustration. You might write the CREATE TABLE like this:

CREATE TABLE products (
  product_id INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  color VARCHAR(10) NOT NULL,
  price FLOAT(6,2),
  PRIMARY KEY (product_id),
  UNIQUE (name),
  INDEX (color))

In this definition, name and color are declared NOT NULL, and this works with earlier versions of MySQL and all table types. However, in version 3.23.2 or later with a MyISAM table type, these could be allowed to have null values, thus the definitions for these columns could legally be

name VARCHAR(30) NULL,
color VARCHAR(10) NULL,

Adding Indexes to Existing Tables

To add an index to a table that already exists, the syntax is similar to creating indexes with CREATE TABLE. You just use ALTER TABLE and add the word ADD before the index specification. You should recall how to alter a table:

ALTER TABLE table_name action_list
							

The action_list would include any or all of the following, each one working like its counterpart when used during table creation:

ADD PRIMARY KEY (column_list)

ADD UNIQUE [index_name] (column_list)

ADD INDEX|KEY [index_name] (column_list)

ADD FULLTEXT [index_name] (column_list)

MySQL also supports CREATE PRIMARY KEY, CREATE UNIQUE, and CREATE INDEX syntax for compatibility with other implementations of SQL. These are identical in operation to the ALTER TABLE forms. See the MySQL Technical Reference for more about using these.

Showing the Indexes on a Table

It's easy to see what indexes exist on a table. You can use either the DESCRIBE or SHOW statements. Using DESCRIBE (or its shortened form, DESC) works like this:

mysql> DESC products;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| product_id | int(11)     |      | PRI | 0       |       |
| name       | varchar(30) |      | UNI |         |       |
| color      | varchar(10) | YES  | MUL | NULL    |       |
| price      | float(6,2)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Using SHOW, you could type this:

mysql> SHOW INDEXES FROM products;
							

Here's a sample of the output (shortened for clarity):

+----------+------------+----------+--------------+-------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name |
+----------+------------+----------+--------------+-------------+
| products |          0 | PRIMARY  |            1 | product_id  |
| products |          0 | name     |            1 | name        |
| products |          1 | color    |            1 | color       |
+----------+------------+----------+--------------+-------------+

As you can see, in both cases the product_id column is shown as the primary key (marked PRI or PRIMARY, respectively), name is a unique index (UNI or Non_unique set to 0), and color is a non-unique index (MUL, for multiple value, or Non_unique set to 1).

You can also see whether a query is using an index, by adding EXPLAIN before a query:

mysql> EXPLAIN SELECT * FROM products WHERE name LIKE "Sweater%";
+----------+--------+---------------+------+---------+------+------+-------+
| table    | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----------+--------+---------------+------+---------+------+------+-------+
| products | system | name          | NULL |    NULL | NULL |    1 |       |
+----------+--------+---------------+------+---------+------+------+-------+

You'll learn how to use the EXPLAIN statement and how to ensure that your queries are making best use of indexes in Day 18, “Optimizing Performance.”

Dropping Indexes

Dropping an index destroys the index and the values it stores but does not affect the data in any indexed columns. Again you can use ALTER TABLE:

ALTER TABLE table_name action_list
							

Your action_list would now include any of the following:

DROP PRIMARY KEY

DROP INDEX index_name
							

For example:

ALTER TABLE products DROP INDEX color

would drop the index called color.

Disabling and Enabling Indexes

MySQL 4.0 brings two new ways of handling indexes on MyISAM tables: within the context of an ALTER TABLE command, you can disable and enable non-unique indexes. The syntax for an item in your action_list would be like this:

DISABLE KEYS

or

ENABLE KEYS

If you are performing a bulk insert, disabling indexes prior to the insert speeds up performance. (Because only non-unique indexes are disabled, unique key integrity is still ensured.) With these indexes disabled, MySQL does not need to create an index entry after inserting each record, thus saving performance overhead.

After the bulk insert is finished, you would enable keys again by using the ALTER TABLE...ENABLE KEYS command, which creates the missing indexes in one go. Overall, this is a far more efficient way to perform a bulk insert of data.

Multiple-Column Indexes

An index can apply to a single column, but you can also create multiple-column indexes: in other words, you can have an index that is a combination of the values of several columns.

As you saw in the syntax for creating indexes during CREATE TABLE and ALTER TABLE, the parameter column_list can contain a list of column names.

For example, in the products table you saw earlier, you could create an index on columns name and color:

CREATE TABLE products (
  product_id INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  color VARCHAR(10),
  price FLOAT(6,2),
  PRIMARY KEY (product_id),
  UNIQUE desc_idx (name,color))

This multiple-column index, desc_idx, would speed up operations that use

name

or

name...AND...color

in the WHERE portion of a SELECT statement, or in a JOIN that uses either combination to relate to another table.

In general, if a multiple-column index has been added on table columns col1, col2, and col3 (although there could be more than three), it speeds up operations that refer to

col1
col1...AND...col2

col1...AND...col2...AND...col3
						

However, operations that refer to col2 but not col1, or col3 but not both col2 and col1, will not use the index. Likewise, operations that have OR instead of AND, such as col1...OR...col2, will not use the index, and thus not be any faster than a table scan.

If you're using multiple-column indexes, it is good practice to place the most restrictive column first. We did this just now in the products table, in which we placed name before color (because a product name is likely to be more specific than a color, which might apply to many products). This helps database performance in any read operation that uses this index.

Partial Indexes

For CHAR and VARCHAR columns, you can create partial indexes. Rather than indexing the entire width of the column, a partial index holds just the first few characters of the values in a column.

In MySQL 3.23.2 and newer versions using the MyISAM table type, you can have partial indexes on BLOB and TEXT columns (which are designed to hold much longer values), but you cannot have indexes on the whole width of the column.

Although theoretically you can index many characters of a text column (or in some cases the whole width), you probably would never need to do this. If you're storing people's names, the headline of a news article, email addresses, or even city names, it's unlikely that you would benefit from storing more than the first 10 characters, after which point the values are likely to be different anyway. Keeping indexes short saves disk space and minimizes the impact on write operations.

You specify a partial index by putting the number of characters to be indexed in parentheses after the name of the column. For example:

ALTER TABLE products
  ADD INDEX (name(8))

creates an index called name on the first eight characters of the values in the column name. It speeds up operations such as

SELECT * FROM products WHERE name LIKE 'Sweater%'