Using MySQL Indexes

To speed up searches and reduce query execution time, MySQL lets you index particular fields of a table. The term “index” here means much the same as in the real world. Similar in concept to the index you find at the end of a book, an index is a list of sorted field values used to simplify the task of locating specific records in response to queries.

In the absence of an index, MySQL needs to scan each row of the table to find the records matching a particular query. This might not cause a noticeable slowdown in smaller tables, but, as table size increases, a complete table scan can add many seconds of overhead to a query. An index speeds up things significantly: With an index, MySQL can bypass the full table scan altogether by instead looking up the index and jumping to the appropriate location(s) in the table. When looking for records that match a specific search condition, reading an index is typically faster than scanning an entire table. This is because indexes are smaller in size and can be searched faster.

That said, an index does have two important disadvantages: It takes up additional space on disk, and it can affect the speed of INSERT, UPDATE, and DELETE queries because the index must be updated every time table records are added, updated, or deleted. Most of the time, though, these reasons shouldn’t stop you from using indexes: Disk storage is getting cheaper every day, and MySQL includes numerous optimization techniques to reduce the time spent on updating indexes and searching them for specific values.

Indexing is typically recommended for fields that frequently appear in the WHERE, ORDER BY, and GROUP BY clauses of SELECT queries, and for fields used to join tables together.

NOTE

With InnoDB tables, MySQL uses intelligent insert buffering to reduce the number of disk writes to InnoDB indexes by maintaining a list of changes in a special insert buffer and then updating the index with all the changes in a single write (rather than multiple simultaneous writes). MySQL also tries to convert the disk-based B-tree indexes into adaptive hash indexes (which can be searched faster), based on patterns in the queries being executed.

Defining an Index

Indexes can be defined either when the table is created or at a later date. To define an index at table creation time, add the INDEX or KEY modifier (the terms are synonymous in MySQL) to the CREATE TABLE statement, as in the following example:

mysql> CREATE TABLE airport (
 -> AirportID smallint(5) unsigned NOT NULL,
 -> AirportCode char(3) NOT NULL,
 -> AirportName varchar(255) NOT NULL,
 -> CityName varchar(255) NOT NULL,
 -> CountryCode char(2) NOT NULL,
 -> NumRunways INT(11) unsigned NOT NULL,
 -> NumTerminals tinyint(1) unsigned NOT NULL,
 -> PRIMARY KEY (AirportID),
 -> INDEX (AirportCode),
 -> INDEX (CountryCode)
 -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.48 sec)

The previous statement builds an index of airport and country codes for the airport list.

To create multifield indexes by concatenating the values of all indexed fields, up to a maximum of 15, specify a comma-separated list of field names in the index modifier, as in the next example:

mysql> CREATE TABLE flightdep (
 -> FlightID SMALLINT(6) NOT NULL,
 -> DepDay TINYINT(4) NOT NULL,
 -> DepTime TIME NOT NULL,
 -> INDEX (DepDay,DepTime)
 -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.19 sec)

Indexes can also be added to an existing table with the CREATE INDEX command. Here’s an example, which creates an index on the AirportID field of the airport table:

mysql> CREATE INDEX AirportID ON airport(AirportID);
Query OK, 15 rows affected (1.02 sec)
Records: 15 Duplicates: 0 Warnings: 0

CREATE INDEX synopsis ON books (synopsis(100));

TIP

If an index name isn’t specified in the INDEX modifier of a CREATE TABLE statement, MySQL automatically names the index using the corresponding field name as the base.

To remove an index, use the DROP INDEX command, as in the next example:

mysql> DROP INDEX AirportID on airport;
Query OK, 15 rows affected (0.24 sec)
Records: 15 Duplicates: 0 Warnings: 0

In addition to the “regular” index type, MySQL supports two other important index variants: UNIQUE indexes and FULLTEXT indexes