Module 1 - Data Storage

Query Optimization

Techniques to make database queries faster and more efficient.

1The GPS Analogy

Simple Analogy

Think of the query optimizer as a GPS. You give it a destination (your query), and it finds the fastest route (execution plan). A good GPS considers traffic (table sizes), road conditions (indexes), and multiple paths to find the optimal route.

2EXPLAIN Your Queries

EXPLAIN shows how the database plans to execute your query. Always analyze slow queries with EXPLAIN before optimizing.

Key Things to Look For

Sequential Scan (Seq Scan)Bad on large tables

Index ScanGood

High row estimatesCheck filters

3Key Optimization Techniques

Add Proper Indexes

Index columns used in WHERE, JOIN, and ORDER BY. Composite indexes for multi-column filters.

✗ WHERE status = 'active' (no index)

✓ CREATE INDEX idx_status ON orders(status)

Avoid SELECT *

Only fetch columns you need. Less data to transfer and process.

✗ SELECT * FROM users

✓ SELECT id, name, email FROM users

Use LIMIT

Paginate results. Don't fetch millions of rows when you need 20.

✗ SELECT * FROM logs

✓ SELECT * FROM logs LIMIT 20 OFFSET 0

Avoid Functions on Indexed Columns

Functions prevent index usage. Move computation to the value side.

✗ WHERE YEAR(created_at) = 2024

✓ WHERE created_at >= '2024-01-01'

4JOIN Optimization

Index Foreign Keys

Always index columns used in JOINs

Reduce JOIN Size

Filter before joining, not after

Consider Denormalization

If JOINs are always the same, embed the data

Use Proper JOIN Types

INNER vs LEFT vs EXISTS depending on need

5Common Anti-Patterns

✗

N+1 Queries

Fetching list then querying each item. Use JOINs or batch fetching.

✗

OR on Different Columns

WHERE a=1 OR b=2 often can't use indexes. Use UNION instead.

✗

LIKE '%value%'

Leading wildcard prevents index use. Consider full-text search.

✗

Missing Pagination

Fetching all rows when you only display 20.

6Key Takeaways

1EXPLAIN every slow query before optimizing

2Index columns used in WHERE, JOIN, ORDER BY

3Avoid SELECT * and always use LIMIT

4Don't apply functions to indexed columns

5Fix N+1 queries with JOINs or batch loading

?Quiz

1. WHERE LOWER(email) = 'test@example.com' is slow because:

2. Best way to optimize a query that does SELECT * FROM products?