Database Indexing Made Easy: SQL vs MongoDB

#sql #mongodb #database

When you search for something in a book, you don’t read every page—you go straight to the index. Databases work the same way. Indexing helps speed up queries by creating a shortcut to the data, instead of scanning the entire table or collection.

🔑 What is Indexing?
Indexing is a technique that improves query performance by reducing the amount of data the database needs to scan.

Without an index → Database checks every row/document (called a full scan)
With an index → Database jumps directly to the relevant data Think of it as a map that points to where your data lives.

🗄️ Indexing in SQL Databases
SQL databases (like MySQL, PostgreSQL, SQL Server) use indexes on tables.
Types of SQL Indexes

Primary Index → Automatically created on the primary key
Unique Index → Ensures no duplicate values
Clustered Index → Sorts and stores rows physically in order of the index
Non-Clustered Index → Creates a separate structure pointing to the data Example in SQL

-- Create an index on the "email" column
CREATE INDEX idx_email ON users(email);

-- Query will now be faster
SELECT * FROM users WHERE email = 'test@example.com';

👉 Without the index, SQL would scan the entire users table. With the index, it jumps straight to the matching row.

🍃 Indexing in MongoDB
MongoDB is a NoSQL database that stores data as documents (JSON-like). Indexes here work similarly but are more flexible.
Types of MongoDB Indexes

Single Field Index → Index on one field
Compound Index → Index on multiple fields
Text Index → For searching text inside documents
Geospatial Index → For location-based queries Example in MongoDB

// Create an index on the "email" field
db.users.createIndex({ email: 1 });

// Query will now be faster
db.users.find({ email: "test@example.com" });

🎯 Why Indexing Matters