Summary: in this tutorial, you will learn how to work with MySQL index and how to take advantages of the index to speed up the data retrieval. We will introduce you several useful statements that allow you to manage MySQL indexes.
A database index, or just index, helps speed up the retrieval of data from tables. When you query data from a table, first MySQL checks if the indexes exist, then MySQL uses the indexes to select exact physical corresponding rows 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 create indices on columns of a table from which you often query the data. Notice that all primary key columns are in the primary index of the table automatically.
If index helps speed up the querying data, why don’t we use indexes for all columns? If you create an index for every column, MySQL has to build and maintain the index table. Whenever a change is made to the rows of 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 adds any column that is declared as
INDEX to the index. In addition, you can add indexes to the tables that already have data.
In order to create indexes, you use the
CREATE INDEX statement. The following illustrates the syntax of the
CREATE INDEX statement:
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:
- For the
UNIQUEindex, MySQL creates a constraint that all values in the index must be unique. Duplicate NULL values are allowed in all storage engines except for BDB.
FULLTEXTindex is supported only by MyISAM storage engine and only accepted on a column whose has data type is
The SPATIALindex 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 its type after the
USING keyword. The name of the index could be
RTREE.You must follow the allowed indices 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|
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 the
CREATE INDEX statement as follows:
CREATE INDEX officeCode ON employees(officeCode)
Besides creating an index, you can also remove index by using the
DROP INDEX statement. Interestingly, the
DROP INDEX statement is also mapped to ALTER TABLE statement. The following is the syntax of removing the index:
DROP INDEX index_name ON table_name
For example, if you want to drop index
officeCode of the
employees table, which we have created above, you can execute the following query:
DROP INDEX officeCode ON employees
In this tutorial, you’ve learned about indexes and how to manage MySQL index including creating and removing indexes.