Indexing in SQL and NoSQL: Improve Database Performance with Simple Tips

 

🔷 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

---

← Previous Next →