Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more the cost of querying. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.
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 other columns in the tables. The statement to create 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],…)
First we have to specify the index based on the table types or storage engine:
- A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL
- FULLTEXT index is supported only by MyISAM storage engine and only accepted columns which have data types – CHAR, VARCHAR or TEXT.
- SPATIAL indexes are used by spatial databases (databases which store information related to objects in space) to optimize spatial queries. MySQL supports spatial column and available only 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 |
| InnoDB | BTREE |
| MEMORY/HEAP | HASH, BTREE |
| NDB | HASH, BTREE |
Finally you need to specify the column in a table to create the index.
In our sample database, we create index to ‘gender’ column on employees table. The SQL statement to create index is as follows:
CREATE INDEX gender_index ON employees(gender);
As you can see, after creating index on ‘gender’ column, there is an improvement in performance.
In general, it is suggested that you should create index on columns you usually use in retrieval such as columns used in ‘join’ and ‘where’ clauses.
DO NOT INDEX ALL COLUMNS unless you need it. Building and maintaining indexes in table take time and storage space on database. In addition when inserting, updating or removing data from table, the index has to be rebuilt and it decreases the performance.
