Part 4 of 5 · 2 min read
Physical plan, and where the optimizer fits
The optimizer chooses concrete algorithms and indexes, turning a logical plan into the physical plan EXPLAIN reports.
Physical plan, and where the optimizer fits
Think of a kitchen manager taking the "combine and bake" recipe. The manager decides, specifically, which mixer to use, which oven, and in which order to prepare the ingredients, based on what is actually available and fastest right now.
In plain words: the optimizer takes the logical plan and chooses specific algorithms and access methods for carrying out each operation: which join algorithm to use, covered on the next page, whether to use a full scan or a specific index, covered throughout Level 5, and in what order to perform joins. The result is the physical plan, the concrete, executable plan that EXPLAIN, covered in Level 5, actually reports. The optimizer makes these choices based on cost estimates, covered later in this level, aiming to pick the cheapest physical plan among many that would all produce the same correct result.
A worked example. For the earlier filter query, the optimizer chooses between a full table scan and an index lookup via idx_orders_customer, picking whichever the cost estimates say is cheaper, producing the physical plan EXPLAIN would display.
The key lines.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- shows the physical plan: the optimizer's concrete choice of scan method
Physical plan, optimizer
The optimizer chooses concrete algorithms and access methods for each operation in a logical plan, producing the physical plan, the specific executable plan that tools like EXPLAIN report.
Example: the kitchen manager's concrete choices of mixer, oven, and order of preparation, based on the "combine and bake" recipe, is the physical-plan equivalent for that dish.
Remember this
Parsing checks and structures the statement; the logical plan says what is needed; the optimizer turns that into a physical plan by choosing how. EXPLAIN from Level 5 shows only this final stage, the physical plan.
Quick notes
- Parsing checks syntax and validates tables and columns, producing a structured representation.
- A logical plan describes relational operations needed, without committing to any algorithm.
- The optimizer chooses concrete algorithms and access methods, producing the physical plan.
EXPLAIN, covered in Level 5, reports the physical plan, the final stage of this pipeline.- The same logical plan can produce different physical plans depending on what the optimizer estimates is cheapest.