A database index is a structure that improves the speed of retrieval of data from our table. Index is similar to the index of a book. If you want to find a chapter you look in the index first without scanning the whole page. Users cannot see the indexes, they are just used by database engines to search records very fast. Indexes can be created on per column basis.

Indexes are basically used to speed up searching operation on a database table. If you are using indexes, then insert and update statement will take more time whereas select statement will become fast.

Ex:

If you have a table with the columns: name, email, address and empID and want to create an index to speed up, then you would need to create an index for empID. When you create this index, MySQL will build a lookup index where queries having empID can be run quickly. However, the name, email and address queries would not be any faster.

How to create Mysql Index:

CREATE TABLE employee_records (
    name VARCHAR(50), email VARCHAR(100),
    empID INT, INDEX (empID)
)

 We can create two types of indexes:

 1. Simple Index

 2. Uniques Index:  A unique index means that two  no two have same index value.

 Ex:

CREATE UNIQUE INDEX employee_index
ON employee_records ( employeeID);

How to display Index Information: We can use show index command  to show all the indexes

SHOW INDEX FROM employee_records