Types of Indexes

There are basically 5 types of indexes.

A unique index is one in which all column values must be unique. In a single column unique index there can be no duplication of values in the column being indexed. In a multi-column unique index the values can be duplicated in a single column, but the combination of column values in each row must be unique. You use a unique index to prevent duplicate values and you often define the index after a table has been created.

primary key is a unique index in which no value can be NULL. Every row must have a value for the column or combination of columns. You would usually define a primary key on the smallest number of columns possible because of this, and most of the time a primary key will be set on a single column. Also, once set the column values in the primary key can’t be changed.

A simple, regular, or normal index is an index where the values don’t need to be unique and they can be NULL. This is the index I’ve mostly been talking about to this point. They’re added simply to help the database find things faster.

A fulltext index, as the name implies, are used for full text searches. Sometimes you want to find the blob of text that contains a certain word or group of words or maybe you want to find a certain substring within the larger block of text.

A descending index (available in version 8+ of MySQL) is a regular index stored in reverse order. It’s helpful when you run queries for the most recently added data like you might to show your five most recent posts or the ten most recent comments on all your posts.

Leave a Reply

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

two + twenty =

Scroll to top