9️⃣ 📌 MySQL Indexing – Speed Up Queries with Primary, Unique & Composite Indexes

Indexing in MySQL is like adding a high-speed search engine to your tables. Without indexes, the database must scan every row to find matching records—causing serious slowdowns as your data grows.

Whether you’re building a CRM, eCommerce platform, or CMS, indexing is key to performance, especially for SELECT, JOIN, and WHERE operations.

---

🧲 Introduction – Why Use Indexing in MySQL?

Indexes improve query performance by avoiding full-table scans. With properly chosen indexes, MySQL can jump directly to relevant rows.

✅ Boosts SELECT speed
✅ Improves JOIN efficiency
✅ Speeds up sorting and filtering
✅ Reduces CPU and memory usage on large datasets

---

📘 Topics Covered

📌 Topic	📄 Description
📍 MySQL Create / Drop / Show Indexes	Learn how to add, remove, and view indexes in tables
🧭 MySQL Index Types	Understand Primary, Unique, Composite, Fulltext, and Prefix indexes

---

🧱 What Is an Index?

An index is a data structure (typically B-tree or hash) that helps MySQL quickly locate rows based on values in indexed columns.

Think of it like a book’s index—jumping to exact topics instead of flipping every page.

---

🔢 Types of Indexes in MySQL

Index Type	Description	Best For
PRIMARY KEY	Unique + NOT NULL, one per table	Row identification
UNIQUE	Prevents duplicate values	Emails, usernames
INDEX / KEY	Non-unique index	WHERE, JOIN, ORDER BY
FULLTEXT	For text search with MATCH ... AGAINST	Blogs, articles
SPATIAL	Indexes geographic data (GEOMETRY types)	Maps, coordinates
COMPOSITE	Multi-column indexes	Complex filters
PREFIX	Indexes partial characters of TEXT/VARCHAR	Optimized for large text fields

---

🔧 1. Creating Indexes

➕ Add Index on Table Creation

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100),
  INDEX (email)
);

➕ Add Index on Existing Table

CREATE INDEX idx_email ON users(email);

🔒 Unique Index

CREATE UNIQUE INDEX idx_username ON users(username);

Prevents duplicates and accelerates WHERE username = ? queries.

🔗 Composite Index

CREATE INDEX idx_name_dept ON employees(last_name, department);

Best for:

SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'HR';

🔤 Prefix Index (TEXT/VARCHAR)

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

Useful when full string indexing is inefficient or unsupported.

---

🔍 2. Viewing Indexes

Use the SHOW INDEXES command:

SHOW INDEXES FROM users;

Returns:

• Index name
• Column(s) indexed
• Uniqueness
• Cardinality (selectivity)

---

❌ 3. Dropping Indexes

Remove an index from a table:

DROP INDEX idx_email ON users;

---

📈 4. Query Optimization with Indexes

Use the EXPLAIN command:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

It shows whether an index is used (key field), and how many rows are scanned (rows field).

---

🧠 5. Indexing Best Practices

✅ Tip	💡 Reason
Index WHERE, JOIN, ORDER BY columns	Improves filter and sorting performance
Keep indexes narrow	Uses less space and speeds up lookup
Avoid indexing everything	Indexes add write/update overhead
Use composite indexes in order	MySQL uses left-most prefix for queries
Use UNIQUE for natural keys	Validates data and speeds up queries

---

🚀 Real-World Use Cases

Scenario	Recommended Index	Why It Helps
Login authentication	UNIQUE(email)	Fast login validation, prevents duplicates
Sorting blog posts	INDEX(published_at)	Optimizes ORDER BY published_at DESC
Filter by city and age	INDEX(city, age)	Fast multi-column filtering
JOIN products to categories	INDEX(category_id) on products	Speeds up joins

---

📉 Index Pitfalls & Anti-Patterns

❌ Mistake	⚠️ Why It’s a Problem
Indexing every column	Slows down inserts and consumes storage
Indexing low-cardinality columns	Index rarely used (e.g., gender, status)
Wrong column order in composite	Breaks index usage on partial matches
Not analyzing EXPLAIN output	You won’t know if your index is helping

---

📌 Summary – Recap & Next Steps

MySQL indexing is one of the most important performance tools in a developer’s toolkit. By carefully planning indexes, you’ll speed up queries, reduce server load, and build scalable applications.

🔍 Key Takeaways

• Use INDEX for filtering, joining, and sorting columns.
• Choose between PRIMARY, UNIQUE, and COMPOSITE based on needs.
• Validate with EXPLAIN to confirm index usage.
• Avoid indexing too many or rarely filtered columns.

⚙️ Real-World Relevance
Used in search engines, analytics platforms, e-commerce databases, and real-time reporting—indexing is essential for performance at scale.

---

❓ FAQ – MySQL Indexing

❓ Does indexing improve INSERT/UPDATE speed?

❌ No. Indexes slightly slow down write operations due to maintenance overhead.

---

❓ Can I index a TEXT or VARCHAR column?

✅ Yes, with a prefix index:

CREATE INDEX idx_bio ON users(bio(255));

---

❓ Difference between UNIQUE and INDEX?

• UNIQUE: Prevents duplicate values.
• INDEX: Boosts search but allows duplicates.

---

❓ How to check if a query uses an index?

Use:

EXPLAIN SELECT ...

Look for values in key, possible_keys, and rows.

---

❓ How many indexes can MySQL support?

✅ Up to 64 indexes per table, including PRIMARY and UNIQUE.

---

Share Now :