What are the indexes in mysql ?

An index helps to get the data from tables very fast. When we run the query to fetch data from a table, MySQL checks if the indexes exist, then MySQL uses the indexes to select exact physical corresponding rows of the table instead of scanning the complete table.

Indexes can be created using one or more columns. Columns use in your SQL queries need to be index. It will speed up your queries.

How to know the index available on the table:

SHOW INDEX FROM tableName.

Create an index on the table(You can use one or more columns to create an index):

CREATE UNIQUE INDEX indexName ON tableName ( column1, column2,...);

We can create the four type of indexes:

1) Ordinary Index

2) Primary Index

3) Unique Index

4) Full-Text Index

Below is the sample statements to add above four types of indexes:

1) Ordinary Index: This is an ordinary index in which any value may appear more than once.

ALTER TABLE tableName ADD INDEX indexName (columnList)

2) Primary Index: Below statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL. 

ALTER TABLE tableName ADD PRIMARY KEY (columnList)

3) Unique Index:  This index must have unique value (with the exception of NULL values, which may appear multiple times)

ALTER TABLE tableName ADD UNIQUE indexName (columnList)

4) Full-Text Index: This creates a special FULLTEXT index that is used for text searching in tables.

ALTER TABLE tableName ADD FULLTEXT indexName (columnList)

How to drop an index?

ALTER TABLE tableName DROP INDEX (indexName);