Course contentsShow
← System Design
Lesson 284 of 1,9198. Database Performance OptimizationFree lesson

Aggregation Query Optimization

Strategies for GROUP BY, COUNT, and SUM queries including covering indexes and materialized aggregates.

Aggregation Query Optimization

What you'll learn: How to make queries using GROUP BY, COUNT, and SUM run efficiently at scale by using covering indexes and pre-computed aggregates.


The Problem with Aggregations

Aggregation queries like "How many orders per customer?" or "Total sales per region?" force the database to scan many rows, group them, and compute results. Without optimization, these queries can become painfully slow as tables grow to millions of rows.

Think of it like counting votes: if you count every ballot one-by-one each time someone asks for results, it takes forever. But if you keep running tallies by district, you can answer instantly.


Strategy 1: Covering Indexes

A covering index includes all columns needed for the aggregation, so the database never touches the actual table rows.

For a query like SELECT region, COUNT(*), SUM(amount) FROM orders GROUP BY region, create an index on (region, amount). The database reads only the compact index, not the full table.

Why it works: Indexes are sorted and compressed, making scanning and grouping much faster.


Strategy 2: Materialized Aggregates

For frequently-run aggregations, pre-compute and store results in a separate table. Update it incrementally when data changes (on insert/update) or periodically via batch jobs.

Example: Instead of calculating SUM(amount) GROUP BY customer_id every time, maintain a customer_totals table updated in real-time or nightly.

Trade-off: You exchange write complexity and slight staleness for dramatic read speed improvements.


When to Use Each

  • Covering indexes: When aggregations vary but use consistent columns
  • Materialized aggregates: When the same aggregations run repeatedly and slight delays are acceptable

Key Takeaway: Optimize aggregations using covering indexes to avoid full table scans, or pre-compute results into materialized aggregate tables when queries are predictable and repeated frequently.