Sample questions
Mysql Query OptimizationDifficulty 1
What does the EXPLAIN statement do when placed in front of a SELECT query in MySQL?
- aIt shows the optimizer's plan for the query, without running it✓
- bIt runs the query and returns both the result rows and a plan summary, timestamped for later comparison
- cIt rewrites the query into an equivalent but faster form and executes that instead
- dIt disables the query cache for that single statement only, leaving the setting unchanged for every other statement
Explanation:EXPLAIN asks the optimizer to produce the execution plan (access type, index used, estimated rows, etc.) it would use for the query, without executing it and returning data rows. It doesn't rewrite-and-run the query (c) or touch the query cache (d, which doesn't exist since MySQL 8.0 anyway) — it returns plan rows and (b) is wrong because plain EXPLAIN never returns the query's own result set.
Mysql Query OptimizationDifficulty 1
A colleague claims running plain EXPLAIN SELECT * FROM orders WHERE customer_id = 5; will actually scan the orders table and return timing information. Is this correct?
- aYes,
EXPLAIN always executes the query and measures real timing - bNo, plain
EXPLAIN only estimates the plan; real timing needs EXPLAIN ANALYZE✓ - cYes, but only for queries that use an index and skip a full table scan
- dNo,
EXPLAIN never works on SELECT statements, only on UPDATE/DELETE
Explanation:Plain EXPLAIN never executes the statement — it only reports the optimizer's estimated plan (estimated rows, access type). To actually run the query and see real timing/row counts per node, you need EXPLAIN ANALYZE (available since MySQL 8.0.18). (a) and (c) wrongly claim plain EXPLAIN executes anything, and (d) is false — EXPLAIN works on SELECT, INSERT, UPDATE, DELETE, and more.
Mysql Query OptimizationDifficulty 1
Since MySQL 8.0.18, what is the key difference EXPLAIN ANALYZE adds compared to plain EXPLAIN?
- aIt only works on
INSERT/UPDATE/DELETE, never on SELECT - bIt shows the query text formatted more readably, but uses the same estimated numbers as
EXPLAIN - cIt actually executes the query and reports real per-node timing and row counts alongside the plan✓
- dIt replaces
EXPLAIN entirely and EXPLAIN alone is deprecated
Explanation:EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the statement and augments each plan node with real measured values — actual time and actual rows — in addition to the estimates, then discards the result set (it prints the plan/timings as text, not the rows). It works on SELECT (c is right, a is wrong), the numbers shown are real not just formatted estimates (b is wrong), and plain EXPLAIN is still fully supported, not deprecated (d is wrong).
Mysql Query OptimizationDifficulty 2
In EXPLAIN output, the type column for a query reads ALL. What does this indicate about how MySQL accesses the table?
- aMySQL used every available index simultaneously to satisfy the query
- bMySQL used a covering index, so it never touched the table's data pages
- cMySQL matched exactly one row using a unique key, the fastest possible access type
- dMySQL performed a full table scan, reading every row✓
Explanation:type: ALL is the least efficient access type shown in EXPLAIN — it means a full table scan, reading every row sequentially with no index narrowing the search. It's the opposite of an efficient lookup like const (exactly one row via a unique/primary key, c) or eq_ref/ref/range, and unrelated to using multiple indexes at once (a) or a covering index (b, which would show Using index in Extra with a non-ALL type).
Mysql Query OptimizationDifficulty 1
In EXPLAIN output, which column tells you which index MySQL actually decided to use for that table access, as opposed to indexes that were merely considered?
- a
key✓ - b
possible_keys - c
type - d
ref
Explanation:The key column shows the index MySQL actually chose to use (or NULL if none). possible_keys (b) only lists candidate indexes the optimizer considered, which may not match what was actually used. type (c) describes the access method (e.g. ref, range, ALL), not the index name, and ref (d) shows which column/constant was compared against the chosen key, not the key's identity.
Mysql Query OptimizationDifficulty 2
What does the key_len column in EXPLAIN output tell you?
- aThe total number of rows stored in the index being used, regardless of the query's
WHERE clause - bThe bytes of the chosen index MySQL actually uses to look up rows✓
- cThe maximum number of characters allowed in the indexed column's definition
- dThe number of distinct index files scanned for the query, one per table involved
Explanation:key_len reports how many bytes of the chosen key are actually used for the lookup — useful for confirming whether a composite index is being used fully or only by its leading column(s). It's not a row count (a), not a column length limit (c, though it's derived from column widths it's about usage not schema limit), and it does not count index files (d). MySQL can combine multiple indexes for one table through the index_merge access method, but key_len still describes the key parts used for the reported access.