MySQL CREATE INDEX: Definition, Syntax, and Examples

In MySQL, an index is used to improve the performance of database queries by allowing faster data retrieval. It creates a structure that helps locate data efficiently without scanning the entire table.

Why Use Indexes?

Speeds up SELECT queries.
Enhances performance for JOIN operations.
Efficient for WHERE, ORDER BY, and GROUP BY operations.
Indexes are automatically used by the MySQL optimizer.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2);

MySQL CREATE INDEX Example

The SQL statement below creates an index named "idx_student_name" on the "name" column in the "students" table:

Example

CREATE INDEX idx_student_name

ON students(name);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

Example

CREATE INDEX idx_student_city

ON students(student_name, city);

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

Example

DROP INDEX idx_student_name ON students;

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.