Writing a query that works is easy; writing a query that works on 100 million rows in under 100 milliseconds is the real challenge. This module focuses on the engine room of SQL performance.
Why Optimization Matters
In a small database, you won’t notice inefficient queries. However, in production environments:
- Cost: Slow queries consume more CPU and RAM, increasing cloud costs.
- UX: Users won’t wait 10 seconds for a page to load.
- Concurrency: Long-running queries hold locks, blocking other users and potentially causing deadlocks.
What We Will Cover
- Indexes: The data structure that helps the database find data without scanning the entire table.
- Execution Plans: Tools like
EXPLAIN ANALYZEto see how the database runs your query. - Sargability: Writing conditions so they can actually use indexes.
- OLTP vs OLAP: Understanding the architectural split between transactional and analytical workloads.
The Cost of Indexes
| Benefit | Drawback |
|---|---|
| Faster SELECT queries | Slower INSERT/UPDATE/DELETE |
| Efficient ORDER BY and JOINs | Consumes disk space |
| Enforces UNIQUE constraints | Index maintenance overhead |
The art of database optimisation is balancing read performance against write overhead.
The Query Lifecycle
When you send a query, the database:
- Parses the SQL text.
- Rewrites it (optimiser applies transformations).
- Plans the execution (chooses index scans, join orders, etc.).
- Executes the plan.
- Returns the result.
Understanding EXPLAIN ANALYZE output helps you see which step is the bottleneck.
Q: If indexes make queries faster, why not index every column? A: Indexes have a cost.
Every time you INSERT, UPDATE, or DELETE data, the database must also update the index.
Too many indexes slow down write operations and consume significant disk storage.
Q: What is a full table scan? A: A full table scan occurs when the database engine has
to read every single row in a table to find the requested data. For large tables, this is the most common cause of slow performance.
Q: What does EXPLAIN do? A: EXPLAIN (or EXPLAIN ANALYZE) shows the execution plan of
a query: which indexes are used, join methods, row estimates, and actual costs. It is the first tool to use when investigating a slow query.
Premium Content
Unlock Indexes & Performance Overview and all premium lessons with a subscription.
From ₹199.99/year — See plans