SovranCode
SQL: Query, Model, and Analyze Data EXPLAIN and query plans
This device
Course contentsEXPLAIN and query plans · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONIndexes and access paths
NEXT LESSONQuery tuning patterns
Performance and administration · Lesson 26 155 min

EXPLAIN and query plans

Last lesson built indexes and asked you to spot SCAN versus SEARCH. This lesson reads the whole recipe: which table is outer, whether a join multiplies rows, when SQLite sorts into a temp tree, when UNION pays for distinct, when a subquery repeats, and how ANALYZE feeds those choices.

Plans are not stopwatches

This runner is a handful of rows. A SCAN of three customers is cheap. The words in the plan still name the work you would pay at a million rows. PostgreSQL's EXPLAIN ANALYZE runs the query and prints actual times. SQLite's EXPLAIN QUERY PLAN does not. Treat the plan as a hypothesis, then time on realistic data outside this sandbox.

A plan is the engine's recipe

You write what you want: Ada's city. The engine chooses how: open the unique email index, jump to ada@example.com, then fetch city from the table. That how is the query plan. Two databases, or one database after ANALYZE, can pick different recipes for the same SQL.

Read a plan the way you read a kitchen card: first ingredient, then the next action. Do not start by optimizing milliseconds. Start by checking the recipe matches the question.

EXPLAIN is bytecode; EXPLAIN QUERY PLAN is the map

SQLite has two flashlights.

  • EXPLAIN SELECT ... dumps virtual-machine opcodes: SeekGE, IdxGT, Column. That is how the engine is implemented.
  • EXPLAIN QUERY PLAN SELECT ... prints a short tree: SEARCH customers USING INDEX ... (email=?). That is the map.

Columns on the plan result are id, parent, notused, and detail. Indent in your head using parent: children are steps of the parent. Live in detail. Use raw EXPLAIN when you need to know whether a covering index avoided a table seek. Run both on Ada's email lookup. Expect a long opcode list, then one SEARCH line that you can actually teach.

SQL BROWSER RUNNER

Compare EXPLAIN opcodes with EXPLAIN QUERY PLAN

Same SELECT by email: bytecode first, then the English plan.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

EXPLAIN
SELECT customer_id, city
FROM customers
WHERE email = 'ada@example.com';

EXPLAIN QUERY PLAN
SELECT customer_id, city
FROM customers
WHERE email = 'ada@example.com';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which output would you send a teammate, and which index name appears in the SEARCH line?

Nested loops: outer row, then inner lookup

A join is usually a nested loop. SQLite finds rows in the outer table, then for each outer row searches the inner table. In the plan, the first SCAN/SEARCH is the outer loop. The next line, indented in spirit, is the inner loop.

Here the outer side should be one customer (unique email). The inner side should be that customer's orders, using idx_orders_customer. If the inner line still says SCAN orders, you are rereading every order for Ada. Last lesson's foreign-key index is this loop's inner SEARCH.

SQL BROWSER RUNNER

Read outer SEARCH then inner SEARCH

Ada by email, then her orders through idx_orders_customer.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.email, o.sku
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.email = 'ada@example.com';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which table is outer, which is inner, and what would SCAN orders after a customer SEARCH mean?

A missing join condition multiplies rows

FROM customers AS c, orders AS o with no match is a Cartesian product: every customer with every order. Three customers and four orders become twelve pairs. The plan often SCANs both sides. It will not print “you forgot ON.” The COUNT will.

Filter in WHERE after a comma join can hide the bug: WHERE c.email = 'ada@example.com' still attaches Ada to every order unless you also constrain o.customer_id. Write JOIN ... ON so the match is visible in SQL and in the plan.

SQL BROWSER RUNNER

Count a comma join with no match key

EXPLAIN the Cartesian product, then COUNT the pairs.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

EXPLAIN QUERY PLAN
SELECT c.email, o.sku
FROM customers AS c, orders AS o;

SELECT COUNT(*) AS pair_count
FROM customers AS c, orders AS o;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is pair_count 12, and what ON clause would make each order appear once?

Prove the inner side changed

Optimization is a before-and-after plan, not a feeling. Explain the Ada join with no order index. Expect SEARCH customers, SCAN orders. Create idx_orders_customer. Explain again. Expect SEARCH on that index. Same SELECT text. Different recipe. That is how you know the index was not decorative.

SQL BROWSER RUNNER

Explain the join, index the child, explain again

Inner SCAN of orders, then SEARCH using idx_orders_customer.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

EXPLAIN QUERY PLAN
SELECT c.email, o.sku
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.email = 'ada@example.com';

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.email, o.sku
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.email = 'ada@example.com';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which detail line changed, and why did the SELECT list stay the same?

TEMP B-TREE means extra sort or unique work

If the engine needs an order it does not already have, SQLite builds a temporary B-tree. USE TEMP B-TREE FOR ORDER BY is a sort. An index on city can replace that with SCAN customers USING INDEX idx_customers_city: walk the catalog already in city order.

Temp trees also appear for DISTINCT and for UNION (distinct). They are not errors. They are extra memory and CPU after the read. If you ORDER BY the primary key you already used as the access path, you often avoid them.

SQL BROWSER RUNNER

Replace an ORDER BY temp tree with a city index

Plan ORDER BY city, add idx_customers_city, plan again.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

EXPLAIN QUERY PLAN
SELECT email
FROM customers
ORDER BY city;

CREATE INDEX idx_customers_city ON customers(city);

EXPLAIN QUERY PLAN
SELECT email
FROM customers
ORDER BY city;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Where did USE TEMP B-TREE go, and is the second plan still allowed to say SCAN?

UNION pays for distinct; UNION ALL does not

A COMPOUND QUERY plan has a left-most subquery, then UNION or UNION ALL. UNION adds USING TEMP B-TREE because the combined list must be unique. UNION ALL concatenates. Last lesson on set operations chose meaning; this lesson shows the bill.

If both sides cannot overlap, or you want duplicates, UNION ALL is the honest cheaper plan. If you need distinct emails from two sources, keep UNION and accept the temp tree.

SQL BROWSER RUNNER

Compare UNION's temp tree with UNION ALL

Same two scans of customer emails, two compound shapes.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

EXPLAIN QUERY PLAN
SELECT email FROM customers
UNION
SELECT email FROM customers;

EXPLAIN QUERY PLAN
SELECT email FROM customers
UNION ALL
SELECT email FROM customers;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which compound step uses a TEMP B-TREE, and when is that cost the feature you asked for?

A correlated subquery repeats per outer row

CORRELATED SCALAR SUBQUERY means: for this customer, run that inner SELECT. Three customers, three counts. With idx_orders_customer each inner run is a SEARCH. Without it, each inner run SCANs orders. That is N scans, not one.

A join plus GROUP BY can compute the same counts in one pass. Neither shape is always faster. The plan tells you whether the inner work is a jump or a walk. PostgreSQL often decorrelates; SQLite shows the correlation honestly.

SQL BROWSER RUNNER

See a correlated COUNT per customer

Outer scan of customers, inner SEARCH of orders by customer_id.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.email,
  (SELECT COUNT(*)
   FROM orders AS o
   WHERE o.customer_id = c.customer_id) AS order_count
FROM customers AS c;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many times can the inner SEARCH run, and what index made it a SEARCH?

Covering means the index already has the columns

USING COVERING INDEX means SQLite answered from the index alone. Unique email stores the email key. SELECT email WHERE email = ... can cover. SELECT city WHERE email = ... still SEARCHES that index, then visits the table row for city. The second plan drops the word COVERING.

That extra heap visit is why “add covering columns” is a real design: INDEX (email, city) could cover both lookups, at write cost you already met.

SQL BROWSER RUNNER

Cover SELECT email, then fetch city from the table

Two plans on the unique email index, two SELECT lists.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

EXPLAIN QUERY PLAN
SELECT email
FROM customers
WHERE email = 'ada@example.com';

EXPLAIN QUERY PLAN
SELECT city
FROM customers
WHERE email = 'ada@example.com';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which plan still says COVERING, and which column forced a table visit?

ANALYZE stores measured counts

SQLite guesses join order and index choice using stored statistics when they exist. ANALYZE fills sqlite_stat1: table, index, and a stat string that starts with an estimated row count. After ANALYZE, explain again. On this tiny catalog, SELECT sku WHERE customer_id = 1 may SCAN orders even though the index exists: four rows are cheaper to walk than to jump, then fetch sku from the heap. SELECT customer_id on the same filter can still SEARCH the covering index. Stats changed the recipe. That is the point.

PostgreSQL's EXPLAIN (ANALYZE, BUFFERS) executes the query and prints actual rows and time. MySQL has EXPLAIN ANALYZE on modern versions. This sandbox cannot show those clocks. Do not confuse “the plan looks nice” with “the dashboard is fast.”

SQL BROWSER RUNNER

Fill sqlite_stat1 then explain an order lookup

ANALYZE, read stats, plan SELECT sku versus covering SELECT customer_id.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_customers_city ON customers(city);

ANALYZE;

SELECT tbl, idx, stat
FROM sqlite_stat1
ORDER BY tbl, idx;

EXPLAIN QUERY PLAN
SELECT sku
FROM orders
WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT customer_id
FROM orders
WHERE customer_id = 1;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: After ANALYZE, why might SELECT sku SCAN four rows while SELECT customer_id still SEARCHES the index?

What other engines print

  • PostgreSQL. Seq Scan, Index Scan, Index Only Scan, Nested Loop, Hash Join, Merge Join. EXPLAIN ANALYZE adds actual time and rows. Startup cost versus total cost is an estimate, not a promise.
  • MySQL. type (ALL, ref, range, const), key, rows, Extra (Using filesort, Using temporary). Same story: ALL plus a huge rows estimate is last lesson's SCAN.
  • SQLite. The dialect in this runner. No hash join in ordinary query plans. Nested loop plus temp B-trees is the toolkit.

What you should be able to do

  • Choose EXPLAIN QUERY PLAN before opcode dumps.
  • Name outer versus inner in a nested loop.
  • Catch a Cartesian product with COUNT and the plan.
  • Treat TEMP B-TREE, UNION, and correlated subqueries as named extra work.
  • Confirm an index by a second plan, then ANALYZE on real size.

Independent lab: stop pairing Ada with every order

The starter comma-join keeps Ada's email filter and still attaches every order. Explain it. Rewrite with an explicit join on customer_id, explain again, and add the inner index if SCAN remains. Optional: write the same Ada roster as a grouped join and compare that plan with the correlated COUNT from earlier.

SQL BROWSER RUNNER

Rewrite the comma join and prove the new plan

Start from Ada filtered against every order. Join, index, re-explain.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO customers (email, city) VALUES
  ('ada@example.com', 'London'),
  ('grace@example.com', 'New York'),
  ('linus@example.com', 'Helsinki');

INSERT INTO orders (customer_id, sku, cents) VALUES
  (1, 'NB-1', 1200),
  (1, 'PEN-2', 400),
  (2, 'NB-1', 1200),
  (3, 'MUG-9', 900);

-- 1. Explain the comma join. Predict pair_count.
-- 2. Rewrite as JOIN ... ON o.customer_id = c.customer_id
--    for Ada only, and explain again.
-- 3. Add idx_orders_customer if the inner side still SCANs.
-- 4. Optional: replace a correlated COUNT with a GROUP BY join
--    and compare the two plans.

SELECT c.email, o.sku
FROM customers AS c, orders AS o
WHERE c.email = 'ada@example.com';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What was pair-shaped about the first plan, and which SEARCH lines prove Ada only meets her own orders?

Common mistakes to avoid

  • Reading opcode EXPLAIN when you still cannot say SCAN versus SEARCH.
  • Trusting a “fast” run of four rows and never explaining the production SQL.
  • Leaving a comma join because the WHERE mentions one table.
  • Creating an index and not explaining the same statement afterward.
  • Using UNION when UNION ALL matches the meaning, then blaming the database for a temp tree.
  • Ignoring CORRELATED SCALAR SUBQUERY on a table that still SCANs.
  • Treating SQLite's plan vocabulary as PostgreSQL's EXPLAIN ANALYZE times.

Lesson review

  • I can say a plan is the engine's how, not a rewrite of my SELECT.
  • I can prefer EXPLAIN QUERY PLAN over bytecode until I need opcodes.
  • I can read outer then inner in a nested loop.
  • I can spot a Cartesian product and a TEMP B-TREE sort.
  • I can contrast UNION with UNION ALL and a correlated subquery with a join.
  • I can use covering versus heap fetch, then ANALYZE, without confusing plans with stopwatches.
KNOWLEDGE CHECK

Check your plan reading

Answer all ten questions, then reopen the runner whose TEMP B-TREE or nested loop still feels unclear.

01What is a query plan?
02When should you use EXPLAIN QUERY PLAN instead of EXPLAIN in SQLite?
03In a nested-loop join plan, what does the first SCAN/SEARCH line usually mean?
04What does a plan that SCANs both tables with no join key in the detail usually warn you about?
05What does USE TEMP B-TREE FOR ORDER BY mean?
06Why can UNION be more expensive than UNION ALL in the plan?
07What does CORRELATED SCALAR SUBQUERY mean?
08What is a covering index in a plan?
09What does ANALYZE store that EXPLAIN QUERY PLAN can use?
10You added an index and the plan still SCANs. What should you do first?
PREVIOUS LESSONIndexes and access paths
NEXT LESSONQuery tuning patterns
ON THIS PAGEThe recipeTwo EXPLAINsNested loopsCartesian joinsBefore and afterTEMP B-TREEUNION costCorrelated subqueriesCovering indexesANALYZEIndependent labCommon mistakesKnowledge check
Course contents