enuk

MySQL Index

Introduction

Advantages

Disadvantages

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:

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.