MySQL Indexes

A MySQL index is a database object that improves the speed of data retrieval by creating a reference to the values stored in one or more columns of a table. Instead of scanning every row, MySQL uses indexes to locate matching records more efficiently.

• Improve the performance of SELECT queries.
• Speed up searching, sorting and filtering operations.
• Optimize queries that use WHERE, JOIN and ORDER BY clauses.
• Reduce the time required to retrieve data from large tables.

Syntax

Create an Index

CREATE INDEX index_name
ON table_name (column_name);

Drop an Index

DROP INDEX index_name
ON table_name;

Where:

• Index: A database object that improves the speed of data retrieval.
• Primary Index: Automatically created for a PRIMARY KEY.
• Unique Index: Ensures all indexed values are unique.
• Composite Index: An index created on multiple columns.

Working

Example 1: Create an Index

The following statement creates an index on the Department column.

Query:

CREATE INDEX idx_department
ON Employees (Department);

• The idx_department index helps MySQL retrieve rows faster when queries filter or sort using the Department column.

Example 2: Use an Indexed Column

The following query retrieves employees from the HR department.

Query:

SELECT *
FROM Employees
WHERE Department = 'HR';

Output:

MySQL can use the index on the Department column to retrieve matching records more efficiently.

Example 3: Drop an Index

The following statement removes the idx_department index.

Query:

DROP INDEX idx_department
ON Employees;

The index is deleted and MySQL will no longer use it to optimize queries on the Department column.