What is an Index?
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
An Index makes searching data faster. Like a book index helps you find pages quickly.
Simple analogy:
- Without Index: Read every page to find "SQL" (slow)
- With Index: Check index, jump to page 45 (fast)
Why Use Indexes?
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
Problem: Query is slow
SELECT * FROM students WHERE age = 20;
-- Checks all 10,000 students one by one (slow!)Solution: Create Index
CREATE INDEX idx_age ON students(age);
SELECT * FROM students WHERE age = 20;
-- Now super fast! Jumps directly to age 20 studentsHow to Create Index
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
Example 1: Index on Age
CREATE INDEX idx_student_age ON students(age);Now searching by age is fast!
Example 2: Index on Email
CREATE INDEX idx_user_email ON users(email);Searching by email is now fast!
Example 3: Index on Multiple Columns
CREATE INDEX idx_name_grade ON students(name, grade);Searching by name and grade together is fast!
When to Use Indexes
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
Create Index on columns you search often:
- WHERE age = 20 → Create index on age
- WHERE email = 'john@email.com' → Create index on email
- ORDER BY created_at → Create index on created_at
Do NOT create Index on:
- Columns you rarely search
- Very small tables (under 1000 rows)
- Columns with few unique values (like gender: M/F)
How to Drop Index
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
DROP INDEX idx_student_age;Real Examples
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
Example 1: Speed Up Login
CREATE INDEX idx_username ON users(username);Login searches by username are now fast!
Example 2: Speed Up Order Search
CREATE INDEX idx_order_date ON orders(order_date);Searching orders by date is now fast!
Example 3: Speed Up Product Search
CREATE INDEX idx_product_category ON products(category);Filtering products by category is now fast!
Summary
With vs Without Index
| Scenario | Without Index | With Index |
|---|---|---|
| Search 1 million rows | 5 seconds | 0.01 seconds |
| How it works | Checks every row | Jumps to match |
| Speed | Slow | Fast |
3 rows
Index = Makes searching fast
- Like a book index
- Speeds up WHERE and ORDER BY
- Create on columns you search often
- Small tables do not need indexes