Creating Indexes

gilbertsavier · July 31st, 2009, 06:39 AM

Hi,
Database indexes help to speed the retrieval of data from MySQL database server faster. When retrieving the data, MySQL first check whether the indexes exists; If yes it will use index to select exact physical corresponding rows without scanning the whole table.

In general, it is suggested that you should put indexes on columns you usually use in retrieval such as primary key columns and columns used in join and sorts. Why not index every column? The most significant is that building and maintaining an indexes tables take time and storage space on database.

Usually you create indexes when creating tables. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed by MySQL. In addition, you can add indexes to the tables which has data. The statement to create index in MySQL as follows:

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

First you specify the index based on the table types or storage engine:

* UNIQUE means MySQL will create a constraint that all values in the index must be distinct. Duplicated NULL is allowed in all storage engine except BDB.
* FULLTEXT index is supported only by MyISAM storage engine and only accepted columns which have data type is CHAR,VARCHAR or TEXT.
* SPATIAL index supports spatial column and available 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 are the list:
Storage Engine Allowable Index Types
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH

Finally you declare which column on which table using the index.

In our sample database you can add index to officeCode column on employees table to make the join operation with office table faster as follows:

CREATE INDEX officeCode ON employees(officeCode)