Basic Indexing

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE email = 'john@example.com';

After (Optimized Query)

-- Create index on the column being filtered
CREATE INDEX idx_users_email ON users(email);

SELECT * 
FROM users 
WHERE email = 'john@example.com';

Why This Optimization Works

1. Full Table Scani: Without an index, the database must read every single row in the users table to find matching emails. On a table with 1 million rows, this means 1 million disk reads.

2. Index Seeki: An index is like a sorted book index. The database can quickly locate the matching row using the index, typically in logarithmic time O(log n) instead of linear time O(n).

3. B-Tree Structurei: Most databases use B-tree indexes, which maintain sorted data and allow for fast range queries and lookups.

When to Use Indexes

Indexes are most effective when:

• The column is used frequently in WHERE clauses
• The column has high cardinalityi (many unique values)
• Queries return a small percentage of table rows
• The table is large and frequently queried

Index Best Practices

Scenario	Recommendation
Primary Key	Auto-indexed by default
Foreign Keys	Index for join performance
Frequently filtered columns	Add single-column index
Multiple columns in WHERE	Consider composite index