Database Indexing Optimization Guide

Index Fundamentals

Database indexes improve query performance by providing fast lookup paths. They use data structures like B-Trees for efficient searching, but consume storage and slow down writes.

Index Design Principles

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders复合 ON orders(user_id, status);
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Leftmost Prefix Rule

Composite indexes follow the leftmost prefix rule. For index(a, b, c), queries must start with column 'a' to use the index.

Avoiding Index Failures

-- Don't wrap indexed columns in functions
SELECT * FROM users WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

-- Don't use leading wildcards
SELECT * FROM users WHERE name LIKE 'John%';