Adding indexes to tables

Over time, requirements for a software product usually change and affect the underlying database as well. Often the need for new types of queries arises, which makes it necessary to add one or more new indexes to perform these new queries fast enough.

In this recipe, we will add two new indexes to an existing table called books in the library schema. One will cover the author column, the other the title column. The schema and table can be created like this:

mysql> CREATE DATABASE library;
mysql> USE library;
mysql> CREATE TABLE books (
isbn char(13) NOT NULL,
author varchar(64) default NULL,
title varchar(64) NOT NULL,
PRIMARY KEY (isbn)
) ENGINE=InnoDB;

Getting ready

Connect to the database server with your administrative account.

How to do it...

1. Change the default database to library:

   USE library;
   

2. Create both indexes in one go using the following command:

   ALTER TABLE books ADD INDEX IDX_author(author), ADD INDEX IDX_title(title);
   

How it works...

The ALTER...