What is MySQL indexes ?

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookup and efficient ordering of access to records. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

Create Index Syntax:
Create index on column and allow duplicate values.
CREATE INDEX id_index ON table_name(column_name);

//Example
CREATE INDEX id_index ON Tbl_Employee(EmployeeID);
Try It Now

Create Index Syntax:
Create unique index which can't allow duplicate values.
CREATE INDEX UNIQUE INDEX ON table_name(column_name);

//Example
CREATE UNIQUE INDEX city_index ON Tbl_Employee(City);
Try It Now

ALTER command to ADD and DROP INDEX

ALTER ADD INDEX
ALTER TABLE table_name ADD INDEX(column_name);
//Example
ALTER TABLE Tbl_Employee ADD INDEX(City);

ALTER DROP INDEX
ALTER TABLE table_name DROP INDEX(column_name);
//Example
ALTER TABLE Tbl_Employee DROP INDEX(City);

Try It Now

Show Index
Syntax:
SHOW INDEXES FROM table_name;
//Example
SHOW INDEXES FROM Tbl_Employee ;
Try It Now

How does MySQL indexes work?

Database table does not reorder itself every time the query conditions change in order to optimize the query performance: that would be unrealistic. In actuality, what happens is the index causes the database to create a data structure. The data structure type is very likely a B-Tree. Advantages of the B-Tree is that it is sortable. When the data structure is sorted in order it makes our search more efficient.

Test  Employee Table:

EmployeeID	LastName	FirstName	Gender	City
1	Smith	James	Male	Mumbai
2	Johnson	Maria	Female	New York
3	Miller	David	Male	Mumbai
4	Wilson	Maria	Female	Mumbai
5	Thomas	Mary	Female	New York
6	Martin	Steven	Male	New York
7	Lee	Karen	Female	Mumbai

From above table, We have to search Employee fitstname which have city Mumbai then We write query.
SELECT FirstName FROM Tbl_Employee WHERE city= ‘Mumbai’;

In order to non indexing, database search for City = ‘Mumbai’ in all rows of table and return the result. It is time consuming for large data table.

When we apply indexing then index table look like:

EmployeeID	LastName	FirstName	Gender	City
1	Smith	James	Male	Mumbai
3	Miller	David	Male	Mumbai
4	Wilson	Maria	Female	Mumbai
7	Lee	Karen	Female	Mumbai
2	Johnson	Maria	Female	New York
5	Thomas	Mary	Female	New York
6	Martin	Steven	Male	New York

In indexing, the query search only the rows in the column City that have Mumbai and then using the pointer that can go into the table to find the specific row where that pointer lives. The query can then go into the table to retrieve the fields for the columns requested for the rows that meet the conditions.

If the search were presented visually, it would look like this: