MySQL Performance Tuning Interview Questions: Indexes, Plans and Locks
MySQL performance tuning interviews are diagnostic. A query is slow, and everything about indexes, execution plans and locking is reached through working out why.
Quick answer
Start with the outcome you need
You want to explain how you find and fix a database bottleneck in production, including how you confirmed the fix worked.
What to remember
The question is always diagnosis
A query is slow. That is the round. Everything about indexes, plans and locking is reached through it, and candidates who recite index theory without a diagnostic path answer the wrong question.
Have an order you would work in: look at the query, look at the plan, look at the data distribution, look at what else was happening at the time. Saying that order out loud is worth more than any single fact.
Indexes, including when they do nothing
Know the leftmost prefix rule for composite indexes and be able to say which queries a given index serves and which it does not. This is the most commonly asked and most commonly half-answered topic here.
Know what stops an index being used: a function applied to the column, an implicit type conversion, a leading wildcard. Type conversion is the one that bites hardest because the query looks fine and silently scans.
Know what a covering index is and why it changes the cost, and know that more indexes is not better because every write maintains all of them. A candidate who mentions the write cost unprompted stands out.
Reading a plan rather than quoting one
Be able to say what you look at first in an execution plan: access type, estimated rows, whether the index was actually chosen, and whether a temporary table or filesort appears.
Know that estimates come from statistics and can be wrong, and that a plan can change when data distribution changes. Having seen a query degrade after data growth, without the query changing, is a strong story.
Be ready for pagination at a large offset, because it is the most common real performance problem in application code. Know why it degrades and know the keyset approach that avoids it.
Locking, isolation and the contention question
Know which isolation level your system ran at, what anomaly it permits, and whether anyone chose it deliberately. Most candidates do not know, and knowing it is an immediate differentiator.
Know the difference between row locks and gap locks and why an unindexed update can lock far more than the rows it changes. That single fact explains a large share of production deadlocks.
Have one incident: a deadlock, a long transaction blocking others, or a migration that locked a table. What you saw, how you confirmed it, what you changed.
Practice checklist
- 01Write down your diagnostic order for a slow query, before any tool.
- 02For one composite index, list the queries it serves and those it does not.
- 03Name three ways an index is silently not used.
- 04Explain why large-offset pagination degrades and how to avoid it.
- 05State your isolation level and the anomaly it allows.
- 06Prepare one locking incident with evidence and resolution.
Questions to practice aloud
Why did the optimizer ignore this index?
How do you diagnose a database deadlock?
When can an added index make the system worse?
How did you verify that an optimization was safe?
Frequently asked questions
How much of the storage engine internals is expected?+
Enough to reason about behaviour: clustered index structure, why the primary key affects secondary index size, and why row locks attach to index records. Deeper internals are for database-focused roles.
Is it enough to say I would add an index?+
Not on its own. The follow-up asks which columns in which order, what it costs on write, and how you would confirm it was used. The confirmation step is the part most candidates omit.
What if a database team handled tuning at my company?+
Say so, then describe what you did on the application side: query shape, pagination, transaction scope, and what you escalated. Knowing the boundary of your own work is a credible answer.