Adding or Dropping Indexes

8.8.1 Problem

Table lookups are slow. Or INSERTS and UPDATES are slow.

8.8.2 Solution

ALTER TABLE can not only drop or add columns, it can drop or add indexes on those columns. These operations often are useful for improving the performance of a database. Typically, indexing a column that you query frequently helps SELECT statements run faster because the index allows MySQL to avoid full table scans. Dropping indexes can sometimes be useful as well. Whenever a row is modified, MySQL must update any indexes that include the modified columns. If you don't actually use a particular index very much, it's possible that your table is overindexed and that dropping the index will speed up performance of table updates.

8.8.3 Discussion

For the discussion in this section, it's useful to begin again with a new copy of the mytbl example table. Use DROP TABLE and CREATE TABLE to remove the existing version and recreate it in its original form:

DROP TABLE mytbl;
CREATE TABLE mytbl
(
 i INT,
 c CHAR(1)
);

In earlier sections of this chapter, SHOW COLUMNS was used to see the effect of table modifications. For index-changing operations, use SHOW INDEX rather than SHOW COLUMNS. Currently, the table has no indexes, because none were specified in the CREATE TABLE statement:

mysql> SHOW INDEX FROM mytbl;
Empty set (0.00 sec)

8.8.4 Adding Indexes

There are four types of statements for adding indexes to a table:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

The first statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL. The second creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times). The third adds an ordinary index in which any value may appear more than once. The fourth creates a special FULLTEXT index that is used for text-searching purposes. FULLTEXT searches are discussed in more detail in Recipe 4.12.

For those statements that include index_name in the syntax, the index name is optional. If you don't specify a name, MySQL assigns one automatically. column_list indicates which columns to index; it should be a list of one or more column names separated by commas. As simple examples, the first of the following statements creates a single-column index on c, and the second creates a multiple-column index that includes both c and i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

In many cases, indexed columns must be declared NOT NULL. For example, if you create mytbl as an ISAM table type, the preceding ADD INDEX statements will fail because ISAM tables do not allow NULL in any kind of index. And no matter what the table type, columns in a PRIMARY KEY cannot contain NULL values. If you try to add an index and MySQL issues a NULL-related complaint, use ALTER TABLE to change the relevant column or columns to NOT NULL and then try adding the index again. For example, if you try to make column i a PRIMARY KEY as follows, an error results:

mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);
ERROR 1171 at line 5: All parts of a PRIMARY KEY must be NOT NULL;
If you need NULL in a key, use UNIQUE instead

To deal with this, redefine i to be NOT NULL, then try again:

mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);

Alternatively, as the error message indicates, you can create a UNIQUE index rather than a PRIMARY KEY should you wish to allow NULL values in the index.

8.8.5 Dropping Indexes

To drop an index, use one of the following statements:

ALTER TABLE tbl_name DROP PRIMARY KEY;
ALTER TABLE tbl_name DROP INDEX index_name;

Dropping a PRIMARY KEY is easiest, because you need not know the index name:

ALTER TABLE mytbl DROP PRIMARY KEY;

To drop an index that is not a PRIMARY KEY, you must specify the index name. If you don't know the name, use SHOW INDEX. Vertical-format output (specified by G) often is useful with this statement, to avoid long line wraparound:

mysql> SHOW INDEX FROM mytblG
*************************** 1. row ***************************
 Table: mytbl
 Non_unique: 1
 Key_name: c
Seq_in_index: 1
 Column_name: c
 Collation: A
 Cardinality: NULL
 Sub_part: NULL
 Packed: NULL
 Comment:
*************************** 2. row ***************************
 Table: mytbl
 Non_unique: 1
 Key_name: c_2
Seq_in_index: 1
 Column_name: c
 Collation: A
 Cardinality: NULL
 Sub_part: NULL
 Packed: NULL
 Comment:
*************************** 3. row ***************************
 Table: mytbl
 Non_unique: 1
 Key_name: c_2
Seq_in_index: 2
 Column_name: i
 Collation: A
 Cardinality: NULL
 Sub_part: NULL
 Packed: NULL
 Comment:

The Key_name and Seq_in_index values show the index names and the positions of columns within an index. Those values indicate in the preceding output that mytbl has a single-column index named c and a multiple-column index named c_2. (These are the names that MySQL chose for the first two indexes created earlier.) The statement to drop the indexes looks like this:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

This statement illustrates that you can perform multiple actions with a single ALTER TABLE statement, if you separate the actions by commas.

8.8.6 See Also

An alternative to ALTER TABLE for modifying indexes is to use the CREATE INDEX and DROP INDEX statements. Internally, MySQL maps these onto ALTER TABLE statements, so there's nothing you can do with them that you can't do with ALTER TABLE. See the MySQL Reference Manual for more information.