Note: Experienced users may wish to skip this section.

An index in a relational database serves much the same purpose as an index in a book: books are not (typically) organized alphabetically by subject, but are instead organized into logical chapters and parts. This works well until you need to find all references to a specific subject.

When you find yourself in such a situation you look at the index. In the index, the contents of the book are listed in alphabetical order, with a page number displayed for each subject.

Rather than go through the book page by page, you can look a subject up in the index, then move to the page number in question and scan the page for the subject you are interested in.

In a relational database, rows are not stored in a table in any particular order. An index creates a sorted version of a column (or set of columns) from a table that can be searched more efficiently than the table itself.

Each entry in the index stores either the row itself (in the case of InnoDB), or a pointer to the row's location in the data file (similar to a page number in a book index).

Effective indexing can greatly improve the performance of SELECT queries. When a specific column value is searched for, and an appropriate index is not available, the MySQL server must read every row of the table looking for matching rows. When an appropriate index is available, the MySQL server can look in the index for the appropriate values. If you are querying only columns that appear in the index, the MySQL server skips reading the table itself and return data directly from the index.

This chapter covers the following topics:

There are five different index types available for use in MySQL:

Your choice of index usually depends on whether a given column should contain unique values: if a column contains unique values and those values are considered to uniquely identify a row (such as ISBN, UPC, or SSN values), choose a PRIMARY index. If your values are unique, but are not considered to uniquely identify the row, choose a UNIQUE index. If you wish to improve the performance of queries that match values in a column, use a regular INDEX. Only one PRIMARY index is allowed per table, all other index types can occur multiple times in a single table.

In special cases you may need to use FULLTEXT and SPATIAL indexes. If you are searching columns that contain natural language, you should use a FULLTEXT index. An example of natural language would be the titles and bodies of a collection of articles, as opposed to simple textual data such as person or place names, which should be indexed using a regular INDEX. The SPATIAL index is for use with GIS data only.

To use indexes effectively, it is necessary to identify those queries that are executing slowly and are not using indexes, or columns which contain unique values. Slow queries can be identified by directly reviewing the queries you use or by using the MySQL Slow Query Log.

SELECT last_name, first_name FROM actor WHERE last_name = 'Walken'

SELECT film.title, COUNT(inventory.inventory_id) AS Stock
FROM film, inventory
WHERE film.film_id = inventory.film_id
  AND inventory.store_id = 1
GROUP BY film.film_id

Before creating a new index, it is important to know what indexes currently exist for a given table. Index information can be retrieved using either the SHOW syntax or through use of the INFORMATION_SCHEMA. INFORMATION_SCHEMA is a standard method for accessing information, while SHOW is an extension of the SQL standard. Index information can also be browsed using the MySQL GUI tools.

mysql> USE sakila;
Database changed

mysql> SHOW INDEX FROM film\G
*************************** 1. row ***************************
       Table: film
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: film_id
   Collation: A
 Cardinality: 2
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: film
  Non_unique: 1
    Key_name: Title_Description_Fulltext
Seq_in_index: 1
 Column_name: title
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: FULLTEXT
     Comment:
*************************** 3. row ***************************
       Table: film
  Non_unique: 1
    Key_name: Title_Description_Fulltext
Seq_in_index: 2
 Column_name: description
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment:
3 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS
    -> WHERE TABLE_SCHEMA = 'sakila'
    ->   AND table_name = 'film'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: sakila
   TABLE_NAME: film
   NON_UNIQUE: 0
 INDEX_SCHEMA: sakila
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: film_id
    COLLATION: A
  CARDINALITY: 2
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: sakila
   TABLE_NAME: film
   NON_UNIQUE: 1
 INDEX_SCHEMA: sakila
   INDEX_NAME: Title_Description_Fulltext
 SEQ_IN_INDEX: 1
  COLUMN_NAME: title
    COLLATION: NULL
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: FULLTEXT
      COMMENT:
*************************** 3. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: sakila
   TABLE_NAME: film
   NON_UNIQUE: 1
 INDEX_SCHEMA: sakila
   INDEX_NAME: Title_Description_Fulltext
 SEQ_IN_INDEX: 2
  COLUMN_NAME: description
    COLLATION: NULL
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: FULLTEXT
      COMMENT:
3 rows in set (0.00 sec)
  

15.4.3. Displaying Index Information Using MySQL Administrator

Users can also view index information using MySQL Administrator. To view index information, select the desired schema in the Catalogs screen, then select the Schema Indices tab:

Figure 15.1. Displaying index information with MySQL Administrator

The Catalog screen displays the index information for all tables in the selected schema. To view the columns that make up a given index, click the arrow icon to the left of the index name.

Indexes can be created using either a CREATE TABLE, CREATE INDEX or ALTER TABLE syntax. Before creating an index, you must know which table the index will be added to, which column(s) the index will apply to, the type of index you will create, and whether the index will enforce uniqueness.

CREATE TABLE inventory (
  inventory_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  film_id INT UNSIGNED NOT NULL,
  store_id INT UNSIGNED NOT NULL,
  INDEX store_id_index (store_id)
)

CREATE TABLE film_actor (
  actor_id INT UNSIGNED NOT NULL,
  film_id INT UNSIGNED NOT NULL,
  PRIMARY KEY  (film_id, actor_id)
)

 CREATE INDEX last_first_name ON actor (last_name, first_name)

CREATE UNIQUE INDEX unique_manager ON store (manager)
 

ALTER TABLE actor ADD INDEX last_first_name (last_name, first_name)

ALTER TABLE actor ADD UNIQUE unique_manager (manager)

ALTER TABLE staff ADD PRIMARY KEY (staff_id)

 
ALTER TABLE actor ADD UNIQUE unique_manager (manager), ADD PRIMARY KEY (staff_id)
 

 CREATE INDEX lname ON actor (last_name(5))

 SELECT COUNT(DISTINCT column_name) AS distinct_rows,
 COUNT(DISTINCT(LEFT(column_name, N))) AS prefix_distinct 
 FROM table_name

When executing a SELECT query, the MySQL server typically uses only one index per table involved in the query. If the WHERE clause of the query references more than one column, a single-column index may be less than optimal. For example, say you were executing the following query:

SELECT actor_id 
FROM actor 
WHERE last_name = 'Johnson'
  AND first_name = 'Robert'

If the table had an index on the last_name column, the index could be used to narrow the table down to all actors with the last name Johnson, but MySQL would still have to scan all the matched rows to find actors with the first name Robert.

By using a composite index, or an index on multiple columns, the preceding query could be fully optimized. Here is an example of a composite index on the actor table:

CREATE INDEX last_first_name ON actor (last_name, first_name)

With such a composite index, MySQL can first find the last name Johnson in the table, then search for the first name Robert in the matching index entries.

Composite indexes can also be partially used when the columns in the WHERE clause of a query appear in the left-most part of the composite index. For instance, the following query would make use of the composite index we have created:

SELECT first_name 
FROM actor 
WHERE last_name = 'Johnson'

However, the following query would not make use of our composite index:

SELECT last_name 
FROM actor 
WHERE first_name = 'Robert'

The second example does not make use of the composite index because the first_name column is not the left-most part of the index columns.

This rule applies no matter how many parts a composite index has; if you have an index on (columnA, columnB, columnC, columnD), the index will be used on queries that contain the following columns in the WHERE clause: (columnA), (columnA, columnB),(columnA, columnB, columnC), and (columnA, columnB, columnC, columnD). You would not be able to create any queries without columnA and expect the composite index to be used.

For a more detailed description on when an index will be used, see the section titled How MySQL Uses Indexes in the MySQL Reference Manual.

Existing indexes can be dropped using either a DROP INDEX or ALTER TABLE syntax:

DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP PRIMARY KEY
ALTER TABLE table_name DROP INDEX index_name

You can drop multiple indexes in a single ALTER TABLE statement by separating them with commas:

 ALTER TABLE actor DROP PRIMARY KEY, DROP INDEX last_first_name

While regular indexes are effective for many purposes, they are not effective for columns that contain natural language. An index can be used for single word CHAR and VARCHAR columns, but FULLTEXT indexes are designed for finding strings within larger natural language fields.

A FULLTEXT search takes a string and column list and searches the specified columns for the string, returning results ranked by relevancy.

The FULLTEXT index is available for the MyISAM storage engine only.

The syntax for creating a FULLTEXT index is listed in Section 15.5, “Creating Indexes”. Once the index is created, the MATCH ... AGAINST syntax can be used to perform FULLTEXT queries.

The MATCH clause indicates which columns are to be searched. The list of columns in the MATCH clause must be identical to the list of columns in the FULLTEXT index.

The AGAINST clause contains the string being searched for. The string in the AGAINST clause must be a constant string: you cannot use a user variable or search result in the AGAINST clause.

Here is an example of a basic FULLTEXT query that searches for movies in the film table that contain the word army in the title or description columns:

 SELECT title, description FROM film WHERE MATCH (title, description) AGAINST ('army')

*************************** 1. row ***************************
           title: ARMY FLINTSTONES
     description: A Boring Saga of a Database Administrator And a Womanizer who
must Battle a Waitress in Nigeria
1 row in set (0.00 sec)

Results from a query with MATCH ... AGAINST in the WHERE clause will always return in descending order based on relevancy.

Here is the same query performed with a LIKE clause instead:

 SELECT title, description FROM film WHERE title LIKE '%army%' OR description LIKE '%army%'

*************************** 1. row ***************************
           title: ARMY FLINTSTONES
     description: A Boring Saga of a Database Administrator And a Womanizer who
must Battle a Waitress in Nigeria
1 row in set (0.20 sec)

Note the performance improvement provided by the FULLTEXT index.

The MATCH ... AGAINST syntax can also provide relevancy ranking information:

 
 SELECT title, description, MATCH (title, description) AGAINST ('army') AS rank
 FROM film
 WHERE MATCH (title, description) AGAINST ('army')

*************************** 1. row ***************************
      title: ARMY FLINTSTONES
description: A Boring Saga of a Database Administrator And a Womanizer who
must Battle a Waitress in Nigeria
       rank: 6.1943987015493
1 row in set (0.00 sec)

The relevancy scores are based on the weighting of words within the individual rows. Words that occur rarely in the table are ranked higher than words that appear in a large percentage of the rows.

For more information on the FULLTEXT search engine, see the Fulltext Search section of the MySQL Reference Manual.

Sometimes it is not easy to identify which columns of a table to index, even when you have identified the slow queries in your application. The EXPLAIN statement is designed to assist in the query optimization process by providing insight into how the MySQL optimizer handles a specific query.

To analyze a query, precede the query with the EXPLAIN keyword:

 
 EXPLAIN SELECT film.title FROM actor, film, film_actor
 WHERE actor.actor_id = film_actor.actor_id
   AND film.film_id = film_actor.film_id
   AND actor.last_name = 'Walken'\G
 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: sakila.actor.actor_id
         rows: 26
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: sakila.film_actor.film_id
         rows: 1
        Extra:
3 rows in set (0.00 sec)

EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query.

The main things to look out for are rows where the key column is NULL, where the type column is range, index, or ALL, or where the Extra column contains Using filesort or Using temporary. Such queries should be closely examined for proper index usage as they generally indicate that no index is being used.

For additional information on using the EXPLAIN statement, see the EXPLAIN section of the MySQL Reference Manual.