Speeding Up Queries
An index is a data structure that improves the speed of data retrieval operations. Without an index, the database must scan every row in a table (a full table scan) to find matching records. With an index, it can jump directly to the right rows — like using the index at the back of a book instead of reading every page.
Creating Indexes
-- Single column index
CREATE INDEX idx_student_name ON Students(Name);
-- Composite index
CREATE INDEX idx_student_dept_age ON Students(DeptID, Age);
-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_student_email ON Students(Email);
-- Check existing indexes
SHOW INDEX FROM Students; -- MySQL
\di -- PostgreSQL
When to Use Indexes
How Indexes Work (B-Tree)
B-Tree Index Structure:
┌─────────┐
│ 50 │
└─┬─────┬─┘
┌────┘ └────┐
┌────┴────┐ ┌────┴────┐
│ 25 │ │ 75 │
└─┬─────┬─┘ └─┬─────┬─┘
┌──┘ └──┐ ┌──┘ └──┐
┌─┴─┐ ┌───┴┐ ┌─┴─┐ ┌───┴┐
│10 │ │30 │ │60 │ │90 │
└───┘ └────┘ └───┘ └────┘
To find value 30:
Start at root (50) → go left (< 50)
→ node (25) → go right (> 25)
→ found (30)!
Only 3 steps instead of scanning all rows!