Indexing in MySQL is used to speed up and facilitate data retrieval. If an index has been attached to a column, the MySQL data query "jumps" immediately to that particular row, skipping the entire data scan.

It suggested creating index with those columns that will be frequently queried. Because the database has to maintain the index table and rebuild it every time when there is a change made, it is also wise to avoid adding indexes to each and every column.

The following list shows possible index selections:

  • UNIQUES. MySQL will reject any value that has already been inputted in that particular location, therefore UNIQUE indexed columns may not have duplicated values.
  • FULLTEXT. This index is supported for MyISAM engine and accepted on columns that contain CHAR, VARCHAR or TEXT data type.
  • SPATIAL. This index supports only SPATIAL columns, on MyISAM engines, and it may not be a NULL value.

In addition to that to the index list above, it is valid to say that with index, the index type should be also created. These types are available: BTREE, HASH and RTREE, and their allowance depend on the storage engine used as shown in this table:

Engines and index types
Storage Engine Allowable Index Types
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB H

Although the indexes could be created while creating a column, we may also create them afterwards. Example below shows how:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

   USING [BTREE | HASH | RTREE]

   ON table_name (column_name [(length)] [ASC | DESC],...);

During a table creation, MySQL adds indexes automatically to any column that is declared as PRIMARY KEY, KEY, UNIQUE or INDEX.