Indexes in MySQL

There are several types of indexes are available in MySQL:

  • B-Tree Indexes:
  • Normal Indexes – Normal indexes are the most basic indexes, and have no restraints such as uniqueness. It can contain duplicate value.
  • Unique Indexes – Unique indexes are the same as “Normal” indexes with one difference: all values of the indexed column(s) must be unique. You can not add duplicate value in this column but you can add null.
  • Primary keys – Primary keys are unique indexes+Not Null. All values of the index column must be unique and not null. Generally, people uses AUTO_INCREMENT with this.These indexes are almost always added when creating the table. Note that you may only have one primary key per table.
  • Full-text Indexes – Full-text indexes are used by MySQL in full-text searches. Generally, this index is used for search engine kind of utility where you want to search some specific keyword from the Text column.

A B-tree index can be used for column comparisons in expressions like  =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE, IN, IS NULL comparisons. Here, there some scenarios where MySQL will not use index (i.e  NOT IN, IS NOT NULL, LIKE ‘%sql%’ etc).

  • Spacial Indexes (R-tree) – Spacial index is supported by only MyISAM storage engine. Its just like B-tree but the index follow any order not just left to right. This index used for only specific purpose. (I.e MySQL GIS functions)
  • Hash Indexes – Hash index supported only by Memory Storage Engine.

Hash indexes are completely different than B-tree indexes. Here, we can use only equality comparisons where we can use = or != operators and it will be very fast. Unfortunately, we can not use other comparison operators like <, >, <=, >= and that’s why some of the queries which needs range search, will not use these indexes. In the equality comparison also, we need to use whole key for compare and search row while In B-tree index, any leftmost prefix of the key can be used for searching rows.

More details are here: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Leave a Reply

Your email address will not be published. Required fields are marked *