Explain — SQL Technical Interview Questions & Answers

Explain

Explain

  • EXPLAIN SELECT …; shows MySQL’s plan: which table, type (ALL = full scan, ref/range = index), possible_keys, key actually used, rows estimate. Use it when a query is slow.
  • You don’t memorise every extra column. You look: is it using idx_city or scanning students?
  • EXPLAIN ANALYZE (8.0.18+) actually runs the query and shows real time. Don’t ANALYZE a 20-minute update on prod as a joke.
  • Trap: EXPLAIN as a magic ‘optimise’ command that changes data. It only prints a plan.
  • On the example next to this theory: Explain: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: EXPLAIN SELECT … WHERE city='Pune'; look at key vs ALL.

Code Examples for Explain

Explain — sample query

-- Explain
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Explain: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Explain to an Interviewer

What does type=ALL mean?

Full table scan Index not used