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:
