What is a MySQL index and types of Index ?

MySQL Interview Queries MySQL Database MySQL Index

A database index is a data structure that improves the speed of data retrieval operations on a database table but the cost of additional writes and storage space to maintain the index data structure. Indexes improve database performance because index speeding up query processing.

PRIMARY index is called clustered indexes and others indexes are secondary indexes or non-clustered ndexes.
INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.
SELECT statement is fast

By default, MySQL creates the B-Tree index if you don’t assign the index type.Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees index type.
Notes:

Indexes on spatial data types use R-trees;
MEMORY tables also support hash indexes;
InnoDB uses inverted lists for FULLTEXT indexes.

What are the types of indexes in MySQL?

Five Types of Indexes

PRIMARY KEY (Clustered Index) : When you created any table with a primary key then MySQL automatically created a index called PRIMARY. This index is called the clustered index because the index itself is stored together with the data in the same table so Speeds Up Queries.
UNIQUE : A unique index is the all values must be unique in a single column.In multi-column unique index the values can be duplicated in a single column, but the combination of column values in each record(row) must be unique.
INDEX(Ordinary index)
FULLTEXT
DESCENDING INDEX (in version 8+ of MySQL)

#create index syntax
CREATE INDEX index_name ON table_name (column_list)
#display all indexes of table
SHOW INDEX FROM table_name;

Note:
1- If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns
are NOT NULL and InnoDB uses it as the clustered index.
2-If you do not define a PRIMARY KEY or UNIQUE index for your table,InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values.

Advantages and disadvantages of indexes

Reference :

MySQL

What is a MySQL index and types of Index ?

What are the types of indexes in MySQL?

Top Articles