Query Execution Plans
You are a Fresher on-call. A production issue might involve Query Execution Plans. How do you diagnose and mitigate?
Answers use simple, clear English.
Quick interview answer
Mitigate first, then root-cause. Check symptoms against: Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M)..
Detailed answer
Mitigate first, then root-cause. Check symptoms against: Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M).. Validate with: ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost.. Context: 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. 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: Fresher.
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
Open one as its own read / solve / listen card