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.