MySQL Cookbook

Adding or Dropping Indexes

Problem

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

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.

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)

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