The primary purpose of an index is to optimize query performance by facilitating rapid data lookup. Without indexes, MySQL would need to scan the entire table to locate the desired rows, which can be inefficient, especially for large tables.

Types of Indexes:

1.Primary Key: Uniquely identifies each record in a table. There can be only one primary key per table, and it automatically creates a clustered index.

2. Simple | Regular | Normal:  In this type of Index named simple, regular, or normal, the specified column values do not require to be unique and can be NULL. 

CREATE INDEX index_name
ON table_name (column1, column2, ...);

3. Unique Index: Ensures that the values in the indexed columns are unique across the table.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

4. Full-Text: A full-text index is used for full-text searches on text-based columns (e.g., VARCHAR or TEXT). It enables efficient searching of words and phrases within large text fields.

5. Spatial: A spatial index is a specialized type of index used to improve the performance of queries involving spatial data, such as points, lines, and polygons. These indexes are particularly useful when dealing with geographic information systems (GIS) or location-based applications.

6. Descending Index:  It is  is only available in MySQL versions after 8.0. it is used to store data in reverse order. Using this it is easy to find out latest value insert into database.

Creating Indexes on New Table:

CREATE TABLE(
 column1 datatype PRIMARY KEY,
 column2 datatype,
 column3 datatype,
 ...
 INDEX(column_name)
); 

Creating Indexes on Existing Table:

• With CREATE INDEX Statement

CREATE INDEX index_name ON table_name;

• With ALTER Command

ALTER TABLE tbl_name ADD INDEX index_name (column_list);

Drop Index:

DROP INDEX index_name ON table_name;