Course contentsShow
← Computer Science
Lesson 1699 of 2,87238. Database Transactions and IndexingFree lesson

Cost-Based Query Optimization

How query optimizers estimate costs using statistics to choose between execution plans.

Cost-Based Query Optimization

What you'll learn: How database systems use statistics to estimate execution costs and choose the fastest query plan among multiple options.

The Problem

When you run a SQL query, there are often many ways to execute it—different join orders, different index choices, different operators. The query optimizer's job is to pick the best one, but "best" means predicting the future: which plan will actually run fastest?

How Cost-Based Optimization Works

Instead of guessing or using fixed rules, a cost-based optimizer estimates the cost of each possible execution plan using statistics about your data.

Key Statistics Collected

  • Table cardinality: How many rows are in each table
  • Column cardinality: How many distinct values exist in each column
  • Data distribution: Histograms showing value frequency
  • Index selectivity: What percentage of rows match typical conditions

The Cost Estimation Process

  1. Generate candidate plans (from lesson 1698's execution plan operators)
  2. Estimate each plan's cost using formulas like:
    • Disk I/O operations needed
    • CPU processing time
    • Memory usage for sorts/joins
  3. Choose the plan with lowest estimated total cost

Example

For SELECT * FROM orders WHERE customer_id = 5:

  • Plan A: Full table scan (cost: 1000 disk reads if table has 100,000 rows)
  • Plan B: Hash index lookup (cost: 3 disk reads if statistics show high selectivity)

The optimizer picks Plan B because 3 << 1000.

Why Statistics Matter

Outdated statistics lead to wrong cost estimates and slow queries. That's why DBAs regularly run ANALYZE or UPDATE STATISTICS commands.

Key Takeaway: Cost-based query optimizers use collected statistics about table sizes, data distribution, and index selectivity to estimate and compare execution costs, selecting the plan predicted to be fastest.