Hands-On SQL & MongoDB Indexing Tutorial | Query Optimization Exercise

🧪 Hands-On Exercise: Indexing and Query Optimization

---

🎯 Objective:

Understand the impact of indexes by comparing query performance with and without indexing in both SQL and MongoDB.

---

🔹 Scenario:

You're working with a database of 10,000 students, and you need to search for a student by name.

---

✅ A. SQL Exercise (MySQL or PostgreSQL)

📌 Step 1: Create and Populate the Table

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100),
  Class VARCHAR(10),
  Email VARCHAR(100)
);

-- Insert sample data (loop or script to add 10,000+ rows for better realism)
-- Example:
INSERT INTO Students (StudentID, Name, Class, Email)
VALUES
(1, 'Aisha P', '10A', 'aisha@example.com'),
(2, 'Ravi Patel', '10B', 'ravi@example.com'),
-- Add thousands more...
(10000, 'Simran Joshi', '12B', 'simran@example.com');

📌 Step 2: Run Query Without Index

EXPLAIN ANALYZE
SELECT * FROM Students WHERE Name = 'Simran Joshi';

📋 Note: Record the execution time or rows examined.

---

📌 Step 3: Add an Index

CREATE INDEX idx_name ON Students(Name);

---

📌 Step 4: Run the Same Query Again

EXPLAIN ANALYZE
SELECT * FROM Students WHERE Name = 'Simran Joshi';

✅ Compare performance: Look for reduced cost, fewer rows scanned, and faster time.

---

🔍 Example Output (PostgreSQL with Index):

Index Scan using idx_name on students  (cost=0.28..8.30 rows=1 width=52)
  Index Cond: (name = 'Simran Joshi')
Planning Time: 0.080 ms
Execution Time: 0.102 ms

🆚 Compare this with the earlier output (without index), which might look like:

Seq Scan on students  (cost=0.00..194.00 rows=1 width=52)
  Filter: (name = 'Simran Joshi')
Planning Time: 0.095 ms
Execution Time: 5.223 ms

---

✅ B. MongoDB Exercise

📌 Step 1: Insert Sample Data

for (let i = 1; i <= 10000; i++) {
  db.students.insertOne({
    student_id: i,
    name: `Student${i}`,
    class: `10${i % 5}`,
    email: `student${i}@example.com`
  });
}

// Insert the student to query:
db.students.insertOne({
  student_id: 10001,
  name: "Simran Joshi",
  class: "12B",
  email: "simran@example.com"
});

---

📌 Step 2: Run Query Without Index

db.students.find({ name: "Simran Joshi" }).explain("executionStats");

📋 Note the totalDocsExamined and executionTimeMillis.

---

📌 Step 3: Add an Index on Name

db.students.createIndex({ name: 1 });

---

📌 Step 4: Re-run the Query

db.students.find({ name: "Simran Joshi" }).explain("executionStats");

✅ You should see:

• Lower totalDocsExamined

• Faster executionTimeMillis

• WinningPlan using IXSCAN instead of COLLSCAN

---

🔍 Example Output (MongoDB after indexing):

{
  "executionStats": {
    "executionTimeMillis": 1,
    "totalDocsExamined": 1,
    "nReturned": 1
  },
  "queryPlanner": {
    "winningPlan": {
      "stage": "IXSCAN"
    }
  }
}

🆚 Before indexing, the output might show:

{
  "executionStats": {
    "executionTimeMillis": 15,
    "totalDocsExamined": 10001,
    "nReturned": 1
  },
  "queryPlanner": {
    "winningPlan": {
      "stage": "COLLSCAN"
    }
  }
}

---

🧠 Reflection Questions

1. How did indexing affect query speed in SQL and MongoDB?

2. What would happen if you indexed every column? Why is that a bad idea?

3. Which type of queries benefit most from indexing?

---

💡 Try online:

• Test SQL on DB Fiddle
• Experiment on MongoDB Playground

---

🎓 Now You Completed this lab? Great! You're ready to continue with :

• Part 9: SQL Joins vs NoSQL Referencing

← Previous Next →