What is an optimizer?

Learn the role of the query optimizer.

What is the optimizer?

The optimizer takes a SQL query and picks the cheapest execution plan. It generates several plans for the same query and compares their cost.

EXPLAIN SELECT * FROM orders
WHERE customer_id = 5;

The optimizer builds and picks a plan.

All columns are selected from orders. WHERE keeps only the rows where customer_id = 5 holds.

The optimizer is rule-based (legacy) or cost-based (modern). Modern databases are cost-based; they pick the best plan from statistics.

Use EXPLAIN to see the plan for customer_id = 5 on orders.

Show example solution

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

See the plan

____ SELECT * FROM orders WHERE customer_id = 5;

Show answer

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

Next lesson: Statistics