🔷 Part 8: Indexing and Query Optimization in SQL and NoSQL
This post will introduce the basics of indexing in both SQL and MongoDB, why it matters, and how it can make queries much faster — even with large datasets.
📍 Introduction
Imagine searching for a word in a 500-page book with no index — you’d have to flip through every page! Databases face the same issue without indexes.
Indexing helps databases find data faster, making queries more efficient — especially as data grows. In this part, you'll learn how indexing works in SQL and MongoDB, with practical tips to improve performance.
🔸 1. What is an Index in Databases?
An index is a data structure (often a B-tree or hash) that allows fast searching on specific columns or fields.
Without an index, databases perform a full scan, checking each row/document one by one — which is slow.
With an index, the database can jump directly to the matching data.
🔹 2. Indexing in SQL (e.g., MySQL/PostgreSQL)
✅ Creating an Index
CREATE INDEX idx_student_name ON Students(Name);
Now, if you run this query:
SELECT * FROM Students WHERE Name = 'Aisha';
The database will use the idx_student_name index to quickly locate Aisha’s record.
🔧 Best Practices for SQL Indexing
-
Index columns used in WHERE, JOIN, and ORDER BY
-
Avoid indexing every column (slows down inserts/updates)
-
Use composite indexes when querying multiple columns
🔹 3. Indexing in MongoDB
✅ Creating an Index
db.students.createIndex({ name: 1 });
The 1 means ascending order. Now this query is much faster:
db.students.find({ name: "Aisha" });
🧠 MongoDB Index Types
-
Single field index – Basic fast lookup
-
Compound index – On multiple fields
-
Text index – For search in text fields
-
TTL index – For expiring data (e.g., sessions)
🔧 Best Practices for MongoDB Indexing
-
Use indexes on fields frequently used in queries
-
Avoid indexing large, low-selectivity fields (e.g., status = "active")
-
Monitor performance using
explain()method
db.students.find({ name: "Aisha" }).explain("executionStats");
📊 4. Indexing Comparison: SQL vs MongoDB
| Feature | SQL | MongoDB |
|---|---|---|
| Syntax | CREATE INDEX |
createIndex() |
| Storage | Separate B-tree structure | B-tree by default |
| Types of Indexes | Single, Unique, Composite | Single, Compound, Text, TTL |
| Performance Insight | EXPLAIN PLAN |
explain("executionStats") |
🧠 Summary
| Without Indexing | With Indexing |
|---|---|
| Slow, full-table scans | Fast, targeted lookups |
| Poor performance | Scalable queries |
| Good for small data | Essential for large data |
Indexing is like giving your database a map — use it wisely for speed and efficiency.
✅ What’s Next?
In Part 9, we shall explore Relationships and Joins in SQL vs Referencing in NoSQL, so you can model complex data connections effectively.
- Click Next:
hands-on exercise for indexing and optimization