Creating Indexes

[gilbertsavier]

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)

Thanks & regards,
Lokananth

[surreypcsupport]

I generally try and join using the primary key which is usually a clustered index. This is the fastest type of join because clustered indexes only exist on columns where there are no duplicates.

Non-clustered index I use less. The only time you should use one is if you commonly join on a column that is not the primary key and there are large numbers of rows i.e hundreds of thousands in the table. That is the only time you will benefit from the faster querying because of the overhead used to create and maintain indexes.

In my experience most people over use non-clustered indexes.

[alex28]

thanks for this great information it is help full to me to know more about the indexes