Index-300x225Summary: in this tutorial, you will learn how to work with MySQL index and how to take advantages of  MySQL index to speed up the data retrieval. We will introduce several SQL statements to allow you to manage MySQL index.

Database index, or just index, helps speed up the retrieval of data from tables. When you query data from database tables, first MySQL checks if the indexes of tables exists, if yes MySQL then uses the indexes to select exact physical corresponding records of the table instead of scanning the whole table.

A database index is similar to an index of a book. If you want to find a topic, you look up in the index first, and then you open the page that has the topic without scanning the whole book.

It is highly recommended that you should create index on columns of table from which you often query the data. Notice that all primary key columns are in primary index of  the table.

If index helps speed up the querying data, why don’t we use index all columns? If you create index for every column, MySQL has to build and maintain the index table. In addition, whenever a change is made to the table, MySQL has to rebuild the index, which takes time as well as decreases the performance of the database server.

Creating MySQL Index

You often create indexes when you create tables. MySQL automatically add any column that is declared as PRIMARY KEY, KEY, UNIQUE or INDEX to the index. In addition, you can add indexes to the tables that already have data.

In order to create index, you use CREATE INDEX statement. The following illustrates the syntax of the CREATE INDEX statement:

1
2
3
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE]
ON table_name (column_name [(length)] [ASC | DESC],...)

First, you specify the index based on the table type or storage engine:

  • UNIQUE means MySQL will create a constraint that all values in the index must be unique. Duplicate NULL is allowed in all storage engine except BDB.
  • FULLTEXT index is supported only by MyISAM storage engine and only accepted on column that has data type is CHAR,VARCHAR or TEXT.
  • SPATIAL index supports spatial column and is available on MyISAM storage engine. In addition, the column value must not be NULL.

Then, you name the index and type of index after USING clause such as BTREE, HASH or RTREE also based on the storage engine of the table.

Here are the storage engines of the table with the corresponding allowed index types:

Storage Engine Allowable Index Types
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH

Third, you declare table name and a list columns that you want to add to the index.

Example of creating index in MySQL

In the sample database, you can add officeCode column of  the employees table to the index by using CREATE INDEX statement as follows:

1
<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">CREATE INDEX officeCode ON employees(officeCode)</span>

Removing Indexes

Besides creating index, you can also remove index by using the DROP INDEX statement. Interestingly, DROP INDEX statement is also mapped to ALTER TABLE statement. Here is the syntax:

1
DROP INDEX index_name ON table_name

For example, if you want to drop index officeCode of the employee table,  which we have created above, you can execute following query:

1
DROP INDEX officeCode ON employees

In this tutorial, you’ve learned about indexes and how to manage MySQL index including creating and removing indexes.

From http://www.mysqltutorial.org/