MySQL CREATE INDEX Statement

The CREATE INDEX statement in MySQL is used to create an index on one or more columns of a table. Indexes improve the speed of data retrieval by allowing MySQL to locate rows more efficiently without scanning the entire table.

• Improve the performance of SELECT queries.
• Speed up searching, filtering and sorting operations.
• Optimize queries using WHERE, JOIN and ORDER BY clauses.
• Reduce query execution time on large tables.

Syntax

CREATE INDEX index_name
ON table_name (column_name);

Create an Index on Multiple Columns

CREATE INDEX index_name
ON table_name (column1, column2);

Where:

• CREATE INDEX: Creates a new index on a table.
• Index: A database object that improves data retrieval performance.
• Index Name: The name assigned to the index.
• Composite Index: An index created on multiple columns.

Working

Example 1: Create an Index on a Single Column

The following statement creates an index on the Department column.

Query:

CREATE INDEX idx_department
ON Students (Department);

• The index improves the performance of queries that search or filter using the Department column.

Example 2: Use the Indexed Column

The following query retrieves students from the Computer Science department.

Query:

SELECT *
FROM Students
WHERE Department = 'Computer Science';

Output:

Example 3: Create a Composite Index

The following statement creates an index on both the Department and City columns.

Query:

CREATE INDEX idx_department_city
ON Students (Department, City);

Output:

• The composite index improves queries that filter or sort using both Department and City.