MySQL Index

Introduction

Advantages

• Search queries are much faster.
• PRIMARY KEY and UNIQUE help avoid duplicated row data.
• FULL-TEXT optimizes searching against large amounts of text.

Disadvantages

• New entries (INSERT, UPDATE and DELETE) are slower.
• Greater disk storage space required.

Types

PRIMARY KEY

A column or a combination of columns that is uniquely identifying each row. There is only one primary key for a table.

NORMAL

UNIQUE

Specifies that MySQL will create a distinct constraint. A duplicated NULL is allowed in all storage engines except BDB.

FULL-TEXT

Supported only by the MyISAM storage engine. It only accepts columns which have data types: CHAR, VARCHAR or TEXT.

SPATIAL

Supports the spatial column and is available in the MyISAM storage engine. The column value must not be NULL.

Create

Usually Indices are created when creating a new table. Any column in the create table statement that is declared as a PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed automatically by MySQL. In addition, indices can be added to tables of data that already exist. The statement to create an index in MySQL is as follows:

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

Note the following are based on the storage engine:

• BTREE
• HASH
• RTREE

An index is created on the 'employee_number' column on 'employees' table in order to make the record retreaval faster.

CREATE INDEX employee_number ON employees(employee_number)

Conclusion

Indices increase reading speed in large MySQL databases, but they come at the cost of writing speed and disk space.