SQL · Theory

Explain

Theory

319/439

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.

Exam tip

EXPLAIN SELECT … WHERE city='Pune'; look at key vs ALL.

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.

Short notes

DefEXPLAIN shows how MySQL will run the SELECT.
RuleWatch type and key. ALL = scan.
TrapThinking EXPLAIN changes data.

SQL · Theory

Explain

← All stacks

Theory

319/439

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.

Exam tip

EXPLAIN SELECT … WHERE city='Pune'; look at key vs ALL.

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.

Short notes

DefEXPLAIN shows how MySQL will run the SELECT.
RuleWatch type and key. ALL = scan.
TrapThinking EXPLAIN changes data.

Explain

Explain — sample query

Short notes

Questions

Explain

Explain — sample query

Short notes

Questions