Optimizer and EXPLAIN

The optimizer decides whether a query uses the indexes of Indexes. This section reads, measures and corrects its plans on shop grown with a recursive CTE (Recursive CTEs) and seeded RAND(): 100,000 customers (45% in the US), 10,000 products, 500,000 orders (5% cancelled), 1,249,994 order lines and 200,000 reviews, indexed only by the declared keys. Timings come from a WSL2 6 machine (Ubuntu 26.04 225 , MySQL 9.7.2 524 , default 128 MB buffer pool); expect yours to differ.

Subsections

• Cost-Based Optimizer
• Reading EXPLAIN Output
• Access Methods
• The Extra Column
• EXPLAIN ANALYZE
• Nested-Loop and Hash Joins
• Join Order
• Hints
• Sargable Predicates
• Keyset Pagination