Database Indexing Tutorial: SQL & MongoDB Performance

⚡ Database Indexing Tutorial: SQL & MongoDB Performance Guide

🎯 What You'll Learn

By the end of this tutorial, you will:

Understand how indexes speed up queries by 10x–1000x
Create PostgreSQL indexes for relational course data
Build MongoDB indexes for document-based content
Choose between single-column, composite, partial, and unique indexes
Use EXPLAIN ANALYZE to measure real query performance
Apply indexing best practices specific to online education platforms

🤔 Part 1: Why Indexes Matter for Course Platforms

The Problem

Without indexes, every query performs a full table scan. On a platform with 1 million enrollments, finding one student's progress takes seconds instead of milliseconds.

Real-World Impact

Table	Rows	Query Without Index	Query With Index
`users`	500,000	850 ms	0.4 ms
`enrollments`	2,000,000	2,100 ms	0.8 ms
`courses`	10,000	45 ms	0.2 ms
`lesson_progress`	15,000,000	12,000 ms	1.2 ms

🗄️ Part 2: PostgreSQL Indexes for E-Learning

2.1 Single-Column Indexes

Use these when you frequently filter or join on one column.

sql
1-- Find all courses by an instructor
2CREATE INDEX idx_courses_instructor ON courses(instructor_id);
3
4-- Find all enrollments for a student
5CREATE INDEX idx_enrollments_user ON enrollments(user_id);
6
7-- Find all enrollments for a course
8CREATE INDEX idx_enrollments_course ON enrollments(course_id);
9
10-- Find all reviews for a course
11CREATE INDEX idx_reviews_course ON reviews(course_id);
12
13-- Find all progress records for an enrollment
14CREATE INDEX idx_progress_enrollment ON lesson_progress(enrollment_id);

How it works: PostgreSQL creates a B-tree structure that sorts instructor_id values. Instead of scanning all 10,000 courses, it jumps directly to the instructor's records.

Verify with EXPLAIN:

sql
1EXPLAIN ANALYZE
2SELECT * FROM courses WHERE instructor_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

Before index:

Seq Scan on courses  (cost=0.00..184.00 rows=5 width=200) (actual time=0.823..0.845 rows=3 loops=1)

After index:

Index Scan using idx_courses_instructor on courses  (cost=0.29..8.30 rows=5 width=200) (actual time=0.042..0.045 rows=3 loops=1)

2.2 Composite (Multi-Column) Indexes

Use these when you filter on multiple columns together, especially with ORDER BY.

sql
1-- Browse modules in order within a course
2CREATE INDEX idx_modules_course_order ON course_modules(course_id, module_order);
3
4-- Browse lessons in order within a module
5CREATE INDEX idx_lessons_module_order ON module_lessons(module_id, lesson_order);
6
7-- Find published courses by category
8CREATE INDEX idx_courses_category_status ON courses(category_id, status);

How it works: The index first sorts by course_id, then by module_order within each course. This covers both the WHERE course_id = ? filter and the ORDER BY module_order sort in a single index scan.

Example query:

sql
1SELECT * FROM course_modules
2WHERE course_id = 'course-uuid-123'
3ORDER BY module_order;

Verify with EXPLAIN:

sql
1EXPLAIN ANALYZE
2SELECT module_id, title, module_order
3FROM course_modules
4WHERE course_id = 'course-uuid-123'
5ORDER BY module_order;

2.3 Partial Indexes

Use these when you frequently query a subset of rows. Smaller index = faster lookups.

sql
1-- Most queries only need published courses
2CREATE INDEX idx_courses_status_published ON courses(status)
3WHERE status = 'published';
4
5-- Most progress queries look for completed lessons
6CREATE INDEX idx_progress_completed ON lesson_progress(status)
7WHERE status = 'completed';

How it works: The index only contains rows where status = 'published'. If 80% of your courses are drafts, this index is 80% smaller than a full index on status.

Example query:

sql
1SELECT title, slug, price FROM courses
2WHERE status = 'published'
3ORDER BY created_at DESC
4LIMIT 20;

2.4 Unique Indexes

These enforce data integrity while also providing fast lookups.

sql
1-- Enforce one enrollment per user per course
2CREATE UNIQUE INDEX idx_enrollments_unique ON enrollments(user_id, course_id);
3
4-- Enforce one review per user per course
5CREATE UNIQUE INDEX idx_reviews_unique ON reviews(user_id, course_id);
6
7-- Fast slug lookup with uniqueness guarantee
8CREATE UNIQUE INDEX idx_courses_slug ON courses(slug);

2.5 Covering Indexes

Include extra columns so PostgreSQL never touches the actual table.

sql
1-- Course catalog page: only needs title, slug, price, rating
2CREATE INDEX idx_courses_catalog ON courses(status, category_id)
3INCLUDE (title, slug, price, rating_avg, enrolled_count)
4WHERE status = 'published';

How it works: The query planner sees that all needed columns are in the index, so it performs an Index Only Scan — no table access required.

🍃 Part 3: MongoDB Indexes for E-Learning

3.1 Single-Field Indexes

javascript
1// Fast course lookup by slug
2db.courses_content.createIndex({ "slug": 1 }, { unique: true });
3
4// Find courses by tag
5db.courses_content.createIndex({ "metadata.tags": 1 });
6
7// Find lessons by ID within nested arrays
8db.courses_content.createIndex({ "modules.lessons.lesson_id": 1 });
9
10// Find activity logs by user and course
11db.user_activity_logs.createIndex({ "user_id": 1, "course_id": 1 });
12
13// Sort activity by timestamp
14db.user_activity_logs.createIndex({ "events.timestamp": -1 });
15
16// Find discussions for a specific lesson
17db.course_discussions.createIndex({ "course_id": 1, "lesson_id": 1 });

Note: 1 = ascending, -1 = descending. MongoDB uses B-trees by default.

3.2 Compound Indexes in MongoDB

javascript
1// Browse published courses by level and price
2db.courses_content.createIndex({ "metadata.level": 1, "metadata.price": 1 });
3
4// Query: Find intermediate courses under $50
5db.courses_content.find({
6  "metadata.level": "intermediate",
7  "metadata.price": { $lt: 50 }
8});

The ESR Rule for compound indexes:

Equality fields first (level: "intermediate")
Sort fields second
Range fields last (price: { $lt: 50 })

3.3 Text Indexes for Search

javascript
1// Full-text search on course titles and descriptions
2db.courses_content.createIndex({
3  "title": "text",
4  "description": "text"
5});
6
7// Search
8db.courses_content.find({ $text: { $search: "javascript react" } });
9
10// With relevance score
11db.courses_content.find(
12  { $text: { $search: "javascript react" } },
13  { score: { $meta: "textScore" } }
14).sort({ score: { $meta: "textScore" } });

3.4 Multikey Indexes (Array Fields)

javascript
1// Automatically indexes every element in the tags array
2db.courses_content.createIndex({ "metadata.tags": 1 });
3
4// Find courses with specific tags
5db.courses_content.find({ "metadata.tags": "javascript" });
6db.courses_content.find({ "metadata.tags": { $in: ["javascript", "react"] } });

How it works: MongoDB creates an index entry for each element in the array. A course with 5 tags gets 5 index entries.

3.5 TTL Indexes for Activity Logs

Automatically delete old logs to save storage.

javascript
1// Delete activity logs after 90 days
2db.user_activity_logs.createIndex(
3  { "events.timestamp": 1 },
4  { expireAfterSeconds: 7776000 }
5);

📊 Part 4: Index Strategy by Query Pattern

Page/Feature	Query Pattern	Recommended Index
Course catalog	`WHERE status = 'published' ORDER BY created_at`	Partial index on `status`
Instructor dashboard	`WHERE instructor_id = ?`	`idx_courses_instructor`
Student dashboard	`WHERE user_id = ?`	`idx_enrollments_user`
Module page	`WHERE course_id = ? ORDER BY module_order`	Composite `(course_id, module_order)`
Lesson page	`WHERE module_id = ? ORDER BY lesson_order`	Composite `(module_id, lesson_order)`
Progress tracking	`WHERE enrollment_id = ?`	`idx_progress_enrollment`
Course search	`WHERE title ILIKE '%word%'`	Text index or Elasticsearch
Tag filtering	`WHERE tags @> ['javascript']`	MongoDB multikey index

🔍 Part 5: Measuring Index Performance

PostgreSQL: EXPLAIN ANALYZE

sql
1-- Check if your query uses an index
2EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
3SELECT * FROM enrollments
4WHERE user_id = 'user-uuid-123'
5AND status = 'active';

Look for:

Index Scan or Index Only Scan = Good ✅
Seq Scan on large tables = Bad ❌ (add an index)

MongoDB: explain()

javascript
1// Check query execution
2db.courses_content.find({ slug: "fullstack-web-dev" }).explain("executionStats");
3
4// Look for:
5// executionStats.totalDocsExamined (should be 1, not 10,000)
6// executionStats.executionTimeMillis (should be < 10)

🧪 Part 6: Hands-On Exercises

Exercise 1: Fix a Slow Query

Problem: This query takes 3 seconds:

sql
1SELECT * FROM lesson_progress
2WHERE enrollment_id = 'enroll-123'
3AND lesson_id = 'lesson-456';

Solution:

sql
1-- Add composite index
2CREATE INDEX idx_progress_enrollment_lesson ON lesson_progress(enrollment_id, lesson_id);
3
4-- Verify
5EXPLAIN ANALYZE
6SELECT * FROM lesson_progress
7WHERE enrollment_id = 'enroll-123'
8AND lesson_id = 'lesson-456';

Exercise 2: Optimize Course Browse

Problem: Students browse courses by category and level.

sql
1-- Slow query
2SELECT title, slug, price, rating_avg
3FROM courses
4WHERE category_id = 'cat-uuid'
5AND level = 'beginner'
6AND status = 'published'
7ORDER BY rating_avg DESC;

Solution:

sql
1-- Composite index covering filter, sort, and return columns
2CREATE INDEX idx_courses_browse ON courses(category_id, level, status, rating_avg DESC)
3INCLUDE (title, slug, price)
4WHERE status = 'published';

Exercise 3: MongoDB Discussion Lookup

Problem: Finding discussions for a lesson is slow.

javascript
1// Before: 450ms
2db.course_discussions.find({
3  course_id: "course_001",
4  lesson_id: "les_002"
5}).sort({ created_at: -1 });

Solution:

javascript
1// Compound index
2db.course_discussions.createIndex(
3  { course_id: 1, lesson_id: 1, created_at: -1 }
4);
5
6// After: 3ms

⚡ Part 7: Index Maintenance

PostgreSQL

sql
1-- Rebuild index (removes bloat)
2REINDEX INDEX idx_courses_instructor;
3
4-- Check index size
5SELECT
6    schemaname,
7    tablename,
8    indexname,
9    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
10FROM pg_stat_user_indexes
11WHERE tablename = 'courses'
12ORDER BY pg_relation_size(indexrelid) DESC;
13
14-- Remove unused indexes (check pg_stat_user_indexes first)
15DROP INDEX idx_old_unused_index;

MongoDB

javascript
1// Check index sizes
2db.courses_content.stats().indexSizes;
3
4// Rebuild indexes
5db.courses_content.reIndex();
6
7// Drop unused index
8db.courses_content.dropIndex("idx_old_unused");
9
10// List all indexes
11db.courses_content.getIndexes();

🎯 Quick Reference: Create All Essential Indexes

sql
1-- PostgreSQL: Core indexes for course platform
2CREATE INDEX idx_courses_instructor ON courses(instructor_id);
3CREATE INDEX idx_courses_category ON courses(category_id);
4CREATE INDEX idx_courses_status ON courses(status);
5CREATE INDEX idx_courses_slug ON courses(slug);
6CREATE INDEX idx_courses_published ON courses(status, category_id, level) WHERE status = 'published';
7
8CREATE INDEX idx_modules_course_order ON course_modules(course_id, module_order);
9CREATE INDEX idx_lessons_module_order ON module_lessons(module_id, lesson_order);
10
11CREATE INDEX idx_enrollments_user ON enrollments(user_id);
12CREATE INDEX idx_enrollments_course ON enrollments(course_id);
13CREATE INDEX idx_enrollments_status ON enrollments(status);
14
15CREATE INDEX idx_progress_enrollment ON lesson_progress(enrollment_id);
16CREATE INDEX idx_progress_lesson ON lesson_progress(lesson_id);
17
18CREATE INDEX idx_reviews_course ON reviews(course_id);
19CREATE INDEX idx_reviews_user ON reviews(user_id);

javascript
1// MongoDB: Core indexes for content
2db.courses_content.createIndex({ "slug": 1 }, { unique: true });
3db.courses_content.createIndex({ "metadata.tags": 1 });
4db.courses_content.createIndex({ "modules.lessons.lesson_id": 1 });
5db.courses_content.createIndex({ "metadata.category": 1, "metadata.level": 1 });
6
7db.user_activity_logs.createIndex({ "user_id": 1, "course_id": 1 });
8db.user_activity_logs.createIndex({ "events.timestamp": -1 });
9db.user_activity_logs.createIndex({ "events.lesson_id": 1, "events.type": 1 });
10
11db.course_discussions.createIndex({ "course_id": 1, "lesson_id": 1 });
12db.course_discussions.createIndex({ "tags": 1 });
13db.course_discussions.createIndex({ "is_resolved": 1, "created_at": -1 });

Proper indexing transforms a slow course platform into a sub-100ms experience. Start with the core indexes above, measure with EXPLAIN ANALYZE, and add specialized indexes as your query patterns emerge.