MySQL CREATE INDEX Statement

MySQL CREATE INDEX Statement – Complete Tutorial (Beginner → Interview Level)

The CREATE INDEX statement in MySQL is used to create indexes on table columns to speed up data retrieval.

•  Indexes make SELECT, WHERE, JOIN, and ORDER BY operations much faster.

 What is an Index in MySQL?

An index is a data structure that:

• Improves query performance

• Works like a book index

• Allows MySQL to find rows quickly without scanning the whole table

 Indexes do not change data, only improve access speed.

 Why Use CREATE INDEX?

• Faster search queries
•  Faster joins
•  Faster sorting (ORDER BY)
•  Essential for large tables

Slightly slows down INSERT, UPDATE, DELETE

 Basic Syntax

 Simple Example

Create Index on One Column

 Speeds up queries like:

 Create UNIQUE Index

Prevents duplicate values in a column.

Create Index on Multiple Columns (Composite Index)

 Create Index While Creating Table

 Show Indexes

 Drop Index

 CREATE INDEX vs PRIMARY KEY vs UNIQUE (Interview)

 When NOT to Use Index

• Small tables

• Columns with frequent updates

• Columns with very few unique values

 Common Mistakes

•  Indexing every column
•  Indexing small tables unnecessarily
•  Forgetting composite index order
•  Too many indexes (slows writes)

Interview Questions & MCQs (Very Important)

Q1. What is the purpose of CREATE INDEX?

A) Store data
B) Enforce foreign key
C) Speed up queries
D) Encrypt data

Answer: C

Q2. Can a table have multiple indexes?

A) Yes
B) No

Answer: A

Q3. Which index prevents duplicate values?

A) NORMAL
B) PRIMARY
C) UNIQUE
D) CLUSTERED

Answer: C

Q4. Which statement removes an index?

A) DELETE INDEX
B) REMOVE INDEX
C) DROP INDEX
D) ERASE INDEX

Answer: C

Q5. Composite index means?

A) Index on multiple tables
B) Index on multiple columns
C) Index with conditions
D) Index with joins

Answer: B

Q6. Index improves which operation most?

A) INSERT
B) UPDATE
C) SELECT
D) DELETE

Answer: C

 Exam & Interview Tips

•  Index columns used in WHERE, JOIN, ORDER BY
•  Avoid over-indexing
•  Understand UNIQUE vs NORMAL index
•  Composite index order matters

 Summary

• CREATE INDEX improves query performance

• Multiple indexes allowed per table

• UNIQUE index prevents duplicates

• Indexes speed reads, slow writes slightly

• Very important topic for SQL interviews & exams