Skip to main content

Course Progress

Loading...

Indexes and Their Importance

Duration: 45 minutes
Module 3: Session 2

Learning Objectives

  • Understand what indexes are and how they work
  • Learn different types of indexes in MySQL
  • Know when to create indexes (and when not to)
  • Analyze query performance with EXPLAIN
  • Master index best practices for WordPress

What are Database Indexes?

Indexes are like a book's index - they help MySQL find data quickly without scanning every row. They can make queries 100x faster! 🚀

📚
Real-World Analogy
Imagine finding a word in a dictionary:
  • Without index:Read every page until you find it
  • With index:Jump directly to the right section
That's exactly how database indexes work!

How Indexes Work

graph TB Query[SELECT * FROM users WHERE email = 'john@example.com'] Query --> Check{Index on email?} Check -->|No| FullScan[Full Table Scan
Check all 1M rows] Check -->|Yes| IndexLookup[Index Lookup
Binary search] IndexLookup --> Found[Row found in ~20 steps] FullScan --> Found2[Row found after 500K checks] style FullScan fill:#fee2e2,color:#991b1b style IndexLookup fill:#dcfce7,color:#166534

B-Tree Index Structure

MySQL uses B-Tree (Balanced Tree) indexes by default. They work like this:

  • Data is organized in a tree structure
  • Each node can have multiple values and children
  • Tree remains balanced for consistent performance
  • Supports range queries efficiently

Types of Indexes

-- Creating different types of indexes

-- Regular index (most common)
CREATE INDEX idx_last_name ON employees(last_name);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_name ON employees(last_name, first_name);

-- Fulltext index (for text searching)
CREATE FULLTEXT INDEX idx_content ON posts(title, content);

-- Add index to existing table
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- Drop an index
DROP INDEX idx_last_name ON employees;

When to Use Indexes

✅ Good Candidates for Indexes

  • Columns in WHERE clauses
  • Columns used for JOIN operations
  • Columns in ORDER BY clauses
  • Columns in GROUP BY clauses
  • Foreign key columns
  • Columns with high selectivity (many unique values)
⚠️
❌ When NOT to Use Indexes
  • Small tables (< 1000 rows)
  • Columns with few distinct values (gender, status)
  • Columns that are frequently updated
  • Columns rarely used in queries
  • When indexes would be larger than the table

Analyzing Query Performance

Using EXPLAIN to Check Indexes

-- Check if your query uses an index
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Example output:
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ref  | idx_email     | idx_email | 202 | const|    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

-- type column values:
-- 'ALL' = full table scan (bad!)
-- 'index' = full index scan (better)
-- 'range' = index range scan (good)
-- 'ref' = index lookup (very good)
-- 'const' = constant lookup (best!)
💡
Pro Tip
If EXPLAIN shows "Using filesort" or "Using temporary", your query might benefit from an index!

WordPress Index Best Practices

Common WordPress Indexes

-- WordPress already creates these indexes:
-- wp_posts: post_name, post_parent, post_author
-- wp_postmeta: post_id, meta_key
-- wp_users: user_login, user_email
-- wp_comments: comment_post_ID, comment_approved

-- Custom indexes you might add:
-- For custom queries on post meta
CREATE INDEX idx_meta_value ON wp_postmeta(meta_value(20));

-- For user meta queries
CREATE INDEX idx_user_meta ON wp_usermeta(user_id, meta_key);

-- For custom post type queries
CREATE INDEX idx_post_type_status 
ON wp_posts(post_type, post_status, post_date);
📊
Index Impact
A well-placed index can turn a 5-second query into a 0.05-second query. But remember: every index slows down INSERT, UPDATE, and DELETE operations slightly.

Practice: Optimize These Queries

💻
Exercise
Which indexes would you create for these queries?
1. SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
2. SELECT * FROM products WHERE category = 'electronics' ORDER BY price DESC;
3. SELECT COUNT(*) FROM posts WHERE author_id = 5 AND published_date > '2024-01-01';
💡 Click for Solution 
-- Query 1: Composite index on frequently filtered columns
CREATE INDEX idx_user_status ON orders(user_id, status);

-- Query 2: Composite index for WHERE and ORDER BY
CREATE INDEX idx_category_price ON products(category, price);

-- Query 3: Composite index on all WHERE conditions
CREATE INDEX idx_author_date ON posts(author_id, published_date);

Additional Resources