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 allows you to manage MySQL indexes.
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 should create index on columns of 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 records 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 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 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:
UNIQUEmeans MySQL will create a constraint that all values in the index must be unique. Duplicate NULL value is allowed in all storage engine except BDB.FULLTEXTindex is supported only by MyISAM storage engine and only accepted on column that has data type isCHAR,VARCHARorTEXT.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 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 the CREATE INDEX statement as follows:
CREATE INDEX officeCode ON employees(officeCode)
Removing Indexes
Besides creating 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 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.
随时随地看视频
