EXPLAIN & Query Analysis — Concept Check
Questions on EXPLAIN & Query Analysis. EXPLAIN shows the execution plan: type (const, eq_ref, ref, range, ALL), key used, rows scanned, and Extra flags like Using filesort, Using temporary — key inputs for tuning.
Questions in this quiz
8 questions on EXPLAIN & Query Analysis — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A query on the 'orders' table is running slow. EXPLAIN shows 'type: ALL' and 'Extra: Using where'. What is likely the issue?
- AThe query is missing a GROUP BY clause.
- BThe query is using a temporary table unnecessarily.
- CNo suitable index is available for the query condition.
- DThe query is using a composite index incorrectly.
2.You are optimizing a query and see 'Extra: Using filesort'. What should you do to improve performance?
- AAdd an index covering the ORDER BY columns.
- BIncrease the server's memory allocation.
- CUse a temporary table to pre-sort the data.
- DRemove the ORDER BY clause from the query.
3.A developer uses EXPLAIN ANALYZE and notices the estimated rows differ significantly from actual rows. What should they do next?
- AUpdate the table statistics using ANALYZE TABLE.
- BAdd more indexes to the table.
- CIncrease the server's CPU resources.
- DRewrite the query to use fewer joins.
4.In an EXPLAIN output, which access type indicates the best performance?
- Arange
- Bref
- Cconst/system
- DALL
5.After adding an index on 'status', a query still shows 'Extra: Using temporary'. What might be the cause?
- AThe index is not a composite index.
- BThe table statistics are outdated.
- CThe GROUP BY column is not indexed.
- DThe query lacks an ORDER BY clause.
6.A query shows 'type: ref' for the 'orders' table in a JOIN. What does this imply?
- AA full table scan is performed.
- BA unique index lookup is used.
- CA constant value is matched.
- DA non-unique index lookup is used.
7.You see 'Extra: Using index condition' in an EXPLAIN output. What does it indicate?
- AThe query is performing a filesort.
- BThe index is not being used efficiently.
- CA temporary table is being used.
- DThe query is using an index for filtering.
8.What does EXPLAIN ANALYZE provide that regular EXPLAIN does not?
- ADetails about the query's SQL syntax.
- BActual execution timings and row counts.
- CThe server's current load and memory usage.
- DThe exact number of indexes on a table.