MySQL & MariaDB in Depth Module 6
The optimizer, EXPLAIN and query tuning interview questions
Interview questions on the optimizer, EXPLAIN and query tuning: warm-ups, then what each harder question tests and how a good answer starts.
Warm-up questions
Short questions with short answers, each answered in full.
-
Fresher
EXPLAIN reports type ALL and rows 10 for a dashboard query. Is that worth worrying about, and when?
Usually not here. type ALL is the manual's worst value, but it names the shape, not the cost. A scan of 10 rows that fits in memory is a handful of microseconds and cheaper than any index on it, because every index pays its own cost on every write that touches a row. The sign the plan matters is the row count next to the type and the time the statement takes against the service's budget: a type ALL with rows 10 on a configuration table is fine; a type ALL with rows 50,000,000 on a hot query is not. Measure the time before you change anything, and compare it with the write cost of a new index. Reading the Extra column tells you the second half of the story: Using filesort or Using temporary on a type ALL often matter more than the type itself.
From the lesson Reading EXPLAIN in MySQL: plans line by line
Questions with model answers
What each question tests, the first step of a good answer and the follow-ups to expect.
-
Mid to senior
EXPLAIN of SELECT COUNT() FROM orders WHERE total_paise > 490000 on a 50,000-row table reports type ALL, rows 50000 and filtered 33.33, but SELECT COUNT() returns 544. What does rows times filtered mean, and when is the gap big enough to force a change to the schema or the plan?
- What it tests
- Whether the candidate can read a plan's estimates as the optimizer's cost input, reason about the gap between estimate and reality on an unindexed filter, and name the concrete changes that close that gap on MySQL 8.4.
- First step
- Start from what the two numbers mean in the server: rows is the plan's expected row read from statistics, and filtered is the per cent of them the optimizer expects the WHERE to keep. Their product (16,665 here) is the row count the optimizer compares with other plans. Only after naming the product should the candidate talk about why 33.33 is wrong and what to add.
- Follow-ups
-
- Where does the 33.33 % come from, and what statistics would change it?
- What is the first change to try, a histogram or an index, and why?
- After the change, which numbers in EXPLAIN would you check first?
- When would you leave the gap and tune the query another way?
From the lesson Reading EXPLAIN in MySQL: plans line by line
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
All MySQL & MariaDB in Depth interview questions · The MySQL & MariaDB in Depth track