Explain MySQL Index & its type ?

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. 

Five Types of Indexes

When you create an index or add one to an existing table, you’ll create it as one of several 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.

A 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.

You wouldn’t, for example, include a middle_name field in a primary key as not everyone has or uses a middle name and NULL values are to be expected. But you might include both first and last names as a primary key or more likely last_name and address, both of which will always exist, but aren’t likely to be duplicated in combination. You usually define a primary key when the table is created.

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.

Instead of the entire value being indexed as a whole, a fulltext index will index the individual words inside each text block. This makes it faster to find the specific words and phrases inside the whole text.

Index Data Structures

MySQL has options in the type of data structure to use when creating index tables. The indexes can be either clustered or non-clustered. In a clustered index, the rows of data are stored on disk in the same order as the index. Because of this you can only ever have a single clustered index on any table.

A non-clustered index uses pointers to the data. In other words the structure of the index is separate from the structure of the data rows in the table. Because of the separate structures, you can use as many as you want and you can rearrange the index and still have everything pointing to the right place.

Clustered indexes are generally faster to read since you can get all the information from the index and not have to also consult the original table. A non-clustered index requires you find the specific index and use it to look up the data in the table. A clustered index can take longer to write to, especially if the new data requires the existing data to be reorganized for the index.

InnoDB tables in MySQL use the primary key as the clustered index. If no primary key exists, it will look for the first unique index without any NULL values. If it can’t find either, it will create a clustered index based on a synthetic column containing row ID values. All other indexes are non-clustered.