Reading Execution Plans and Optimizing Queries
See how MySQL actually runs your query, then rewrite it to run fast
Big Idea: A
SELECT is a request, not a recipe. You describe what rows you want; the query optimizer decides how to get them - which index to read, whether to scan or seek, and in what order to join tables. EXPLAIN shows you that plan before running; EXPLAIN ANALYZE runs it and reports what really happened. Once you can read these, the fix for most slow queries becomes obvious.
By the end you will be able to:
- read the key columns of
EXPLAIN:type,key,rows,filtered,Extra, - tell a full table scan from an index range scan or a single-row lookup,
- recognise the main join strategies - nested-loop and hash join,
- use
EXPLAIN ANALYZEto compare estimated vs actual rows, - spot and fix the classic slow-query patterns.
0. The example schema
Every query below runs against one small shop database. Create it once and keep it in mind for the rest of the topic.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(80),
city VARCHAR(60),
created_at DATE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20),
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES
(1,'Aida','Bishkek','2023-02-10'),
(2,'Bekturs','Osh','2023-05-01'),
(3,'Cholpon','Bishkek','2024-01-20');
INSERT INTO orders VALUES
(10,1,'2024-03-01','paid', 120.00),
(11,1,'2024-03-04','paid', 55.50),
(12,2,'2024-03-04','pending', 80.00),
(13,3,'2024-04-11','paid', 210.00);Assume both tables are much larger in production (millions of orders). The tiny sample keeps results readable; the plans are what matter at scale.