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.

1️⃣ 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.

2️⃣ Why Use CREATE INDEX?

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

⚠️ Slightly slows down INSERT, UPDATE, DELETE

3️⃣ Basic Syntax



 

4️⃣ Simple Example

Create Index on One Column



 

✔ Speeds up queries like:

SELECT * FROM students WHERE name = 'Amit';

5️⃣ Create UNIQUE Index ⭐

Prevents duplicate values in a column.


❌ Duplicate emails will not be allowed.

6️⃣ Create Index on Multiple Columns (Composite Index)


✔ Useful when queries filter by name AND city.

7️⃣ Create Index While Creating Table


 

8️⃣ Show Indexes

SHOW INDEX FROM students;

9️⃣ Drop Index

DROP INDEX idx_name ON students;

🔟 CREATE INDEX vs PRIMARY KEY vs UNIQUE ⭐ (Interview)

Feature CREATE INDEX PRIMARY KEY UNIQUE
Duplicate allowed Yes ❌ No ❌ No
Multiple per table Yes ❌ One Yes
NULL allowed Yes ❌ No Yes
Purpose Speed Identity Uniqueness

1️⃣1️⃣ When NOT to Use Index ❌

  • Small tables

  • Columns with frequent updates

  • Columns with very few unique values

1️⃣2️⃣ 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

You may also like...