📌 MySQL Create / Drop / Show Indexes – Manage Indexes for Performance

---

🧲 Introduction – Why Manage Indexes in MySQL?

Indexes play a crucial role in improving the speed of SELECT queries, especially when dealing with large datasets. Creating the right indexes reduces lookup time, while dropping unused ones can improve write performance and reduce storage use.

Managing indexes effectively includes:

• Creating indexes on frequently queried columns
• Dropping unnecessary or duplicate indexes
• Reviewing existing indexes using SHOW INDEXES

🎯 In this guide, you’ll learn:

• How to create, drop, and list indexes in MySQL
• Syntax for unique, composite, and prefix indexes
• When to use and not use indexes
• Best practices for index management

---

🛠️ CREATE INDEX – Add an Index to a Table

🔹 Syntax

CREATE [UNIQUE] INDEX index_name ON table_name (column1 [, column2, ...]);

• UNIQUE: Optional keyword to enforce uniqueness
• index_name: Custom name for the index
• column1, column2: Columns to include in the index

---

🔹 Example 1: Basic Index

CREATE INDEX idx_email ON users(email);

Explanation:
Creates a regular (non-unique) index on the email column in the users table.

---

🔹 Example 2: Unique Index

CREATE UNIQUE INDEX idx_username ON users(username);

Explanation:
Prevents duplicate values in the username column while improving lookup speed.

---

🔹 Example 3: Composite Index (Multi-Column)

CREATE INDEX idx_city_age ON users(city, age);

Explanation:
Optimizes queries using both city and age, especially in combination.

---

🔹 Example 4: Prefix Index (for VARCHAR/TEXT)

CREATE INDEX idx_title ON articles(title(100));

Explanation:
Indexes the first 100 characters of the title column—required when indexing long text columns.

---

🔍 SHOW INDEXES – View Existing Indexes

🔹 Syntax

SHOW INDEXES FROM table_name;

🔹 Example

SHOW INDEXES FROM users;

Explanation:
Displays all indexes on the users table, including:

• Key_name: Name of the index
• Column_name: Columns in the index
• Non_unique: Whether duplicates are allowed (0 = unique, 1 = not unique)
• Seq_in_index: Order of columns in composite indexes
• Index_type: B-tree, FULLTEXT, etc.

---

❌ DROP INDEX – Remove an Existing Index

🔹 Syntax

DROP INDEX index_name ON table_name;

🔹 Example

DROP INDEX idx_email ON users;

Explanation:
Removes the idx_email index from the users table, freeing storage and reducing update overhead.

---

🧠 Index Management Summary

Task	Command Example
Create basic index	CREATE INDEX idx_col ON table(col);
Create unique index	CREATE UNIQUE INDEX idx_col ON table(col);
Create composite index	CREATE INDEX idx_multi ON table(col1, col2);
Drop index	DROP INDEX idx_col ON table;
Show all indexes	SHOW INDEXES FROM table;

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Name your indexes meaningfully	Easier to identify and manage later
Use EXPLAIN to validate index usage	Ensures your indexes are actually being used
Drop unused indexes	Saves space and improves write speed
Use composite indexes for multi-column filters	Reduces query execution time
Avoid duplicate or overlapping indexes	Wastes space and complicates query planner

---

🚀 Real-World Use Cases

Scenario	Action Taken
Fast user login by email	CREATE INDEX idx_email ON users(email);
Remove outdated legacy index	DROP INDEX idx_old_field ON reports;
Audit current indexes in orders	SHOW INDEXES FROM orders;
Add search optimization to tags	CREATE INDEX idx_tags ON articles(tags);
Optimize multi-filter query	CREATE INDEX idx_city_age ON users(city, age);

---

📌 Summary – Recap & Next Steps

MySQL index management helps ensure your queries are fast, your schema is clean, and your writes are efficient. Use CREATE, SHOW, and DROP INDEX wisely to balance performance vs. overhead.

🔍 Key Takeaways

• Use CREATE INDEX to speed up searches, joins, and filters
• Use SHOW INDEXES to audit table indexes
• Use DROP INDEX to remove unnecessary or unused indexes
• Choose UNIQUE when you want to enforce value uniqueness
• Test with EXPLAIN to verify effectiveness

⚙️ Real-World Relevance

Proper index management is crucial in e-commerce filters, search engines, user management, analytics, and any app with growing data volume.

---

❓ FAQ – Create, Drop, and Show Index in MySQL

---

❓ Can I create multiple indexes on one table?

✅ Yes, on different columns or combinations of columns.

---

❓ Can I drop the PRIMARY KEY with DROP INDEX?

❌ No. Use:

ALTER TABLE table_name DROP PRIMARY KEY;

---

❓ Can I index a TEXT or BLOB column?

✅ Yes, but you must use a prefix length:

CREATE INDEX idx_summary ON articles(summary(255));

---

❓ Does indexing always improve performance?

⚠️ No. Over-indexing increases insert/update cost. Index only when needed.

---

❓ What’s the max number of indexes per table?

MySQL supports up to 64 indexes per table.

---

Share Now :