How do you create an index on a table column?

Difficulty: 5/10

CREATE INDEX syntax, index types, performance impact

How to Create an Index on a Table Column in MySQL

Creating an index in MySQL improves the speed of data retrieval by allowing MySQL to quickly locate rows without scanning the entire table. You can create different types of indexes depending on your requirements.

Basic Syntax for Creating an Index

1
Using CREATE INDEX: CREATE INDEX index_name ON table_name (column_name);
2
Using ALTER TABLE: ALTER TABLE table_name ADD INDEX index_name (column_name);

Example: Creating a Normal Index

Example: Creating a UNIQUE Index

You can also create multi-column (composite) indexes when your queries filter or sort using multiple columns together.

Example: Composite Index

When to Create an Index

1
When a column is frequently used in WHERE filters.
2
When performing JOINs on a specific column.
3
When using ORDER BY or GROUP BY on large datasets.
4
When enforcing uniqueness (UNIQUE index).

Index creation is straightforward, but choosing the right columns to index is critical to balancing performance and storage.

Scenario Questions

0-2 years experience

Follow-up Questions