Summary: In this tutorial, you will learn how to work with database index in MySQL. You will learn how to use database index properly to speed up the data retrieval and what SQL statements to use to create or drop database index.
Database indexes help to speed up the retrieval of data from MySQL database server. When retrieving the data from a database table, MySQL first checks whether the index of table exists; If yes it will use index to select exact physical corresponding rows without scanning the whole table.
In general, it is suggested that you should create index on columns you usually use in retrieval such as columns used in join and sorts.Note that all primary keys are in primary index of database table.
Why not index every column? The most significant is that building and maintaining an indexes table take time and storage space on database. In addition, when insert/ update or remove data from table, the index has to be rebuilt and it decrease the performance when changing table data.
Creating Indexes
Usually you create index when creating table. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed automatically by MySQL. In addition, you can add indexes to the tables which has data. The statement to create index in MySQL is as follows:
1 |
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name |
2 |
USING [BTREE | HASH | RTREE] |
3 |
ON table_name (column_name [(length)] [ASC | DESC],...) |
First you specify the index based on the table types or storage engine:
- UNIQUE means MySQL will create a constraint that all values in the index must be distinct. Duplicated NULL is allowed in all storage engine except BDB.
- FULLTEXT index is supported only by MyISAM storage engine and only accepted columns which have data type is CHAR,VARCHAR or TEXT.
- SPATIAL index supports spatial column and available in MyISAM storage engine. In addition, the column value must not be NULL.
Then you name the index using index types such as BTREE, HASH or RTREE also based on storage engine. Here is the list:
| Storage Engine | Allowable Index Types |
|---|---|
| MyISAM | BTREE, RTREE |
| InnoDB | BTREE |
| MEMORY/HEAP | HASH, BTREE |
| NDB | HASH |
Finally you declare which column on which table using the index.
In our sample database, you can create index to officeCode column on employees table to make the join operation with office table faster. The SQL statement to create index is as follows:
1 |
CREATE INDEX officeCode ON employees(officeCode) |
Removing Indexes
Beside creating index, you can also remove index by using 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 which we have added to the employees table, just execute following query:
1 |
DROP INDEX officeCode ON employees |
Related posts:
