Query Execution Plans
Compare Query Execution Plans with a common alternative (angle 6). How do you decide in a design review?
Answers use simple, clear English.
Audio N/AQuick interview answer
Baseline: Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning.
Detailed answer
Baseline: Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning. Prefer when pros dominate: Visual plan reveals missing indexes and bad estimates.. Avoid when: Plans vary by engine; hints are last resort.. Core: Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning. Real-time example: Sudden seq scan on million-row table traced to outdated stats after bulk load. Pros: Visual plan reveals missing indexes and bad estimates. Cons: Plans vary by engine; hints are last resort. Common mistakes: Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M). Best practices: ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost. Audience level: Architect.
Full explanation
Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning.
Real example & use case
Sudden seq scan on million-row table traced to outdated stats after bulk load.
Pros & cons
Pros: Visual plan reveals missing indexes and bad estimates. Cons: Plans vary by engine; hints are last resort.
Common mistakes
Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M).
Best practices
ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost.
Follow-up questions
Only answered follow-ups are shown — click to open with full answers