Why Queries Get Slow | Relational Databases

In this chapter

GreenMart's instant report stopped being instant — we'll see exactly why, generating 10,000 rows in one statement to prove a full table scan's cost grows with the table, not with anything in the query itself.

12–15 min

The Problem in Real Life

Mike pulls up a report he's run a hundred times before — nothing fancy, just "which orders belong to this customer." It used to answer before he'd even finished clicking. Today, he watches a loading spinner for the first time in this store's history.

Sarah checks the query itself — it hasn't changed since Act 3. Nothing about the SQL is wrong. What's changed is everything around it: GreenMart went from a handful of orders to real, sustained scale, and the exact same query is now doing dramatically more work to answer the exact same question.

This used to load instantly!

Mike

A Handful of Rows vs. Real Scale

The query never changed

The exact same SQL that felt instant at 5 rows is the SQL now taking real time at 10,000 — nothing in the query itself is different.

A full scan checks every row, on purpose

Without an index, SQLite has no way to know which rows might match without actually looking at each one.

Cost grows with scale, not with complexity

A simple WHERE clause on a huge table can cost far more than a complex query on a small one.

Nothing warns you until it's already slow

A full scan works correctly at every table size — it just quietly gets more expensive as the table grows, with no error or warning along the way.

Why Do Queries Actually Get Slow?

Without an index, answering "which orders belong to customer 250" means checking every single row in the table, one at a time, to see if it matches — a full table scan. With 5 orders, that's 5 checks, done before anyone could notice. With 10,000 orders, it's 10,000 checks for the exact same question.

This is the whole story behind "it used to be instant": the work a full table scan requires grows in direct proportion to how many rows exist. Nothing about the query changed. The table just grew, and a scan that was invisible at 5 rows is a real, measurable cost at scale.

EXPLAIN QUERY PLAN shows the strategy SQLite actually picks to answer a query, without running it — for each table involved, it reports either SCAN (checking rows one by one) or SEARCH (a faster, targeted lookup).

GreenMart's Real Orders Table, Today

EXPLAIN QUERY PLAN
SELECT * FROM Orders WHERE CustomerID = 101;

With Orders this small, the plan says SCAN Orders — and it barely matters, since scanning 5 rows takes no real time either way.

A recursive CTE can generate thousands of rows in one statement — this builds 10,000 orders across 500 different customers, standing in for the scale GreenMart has actually reached.

Simulating GreenMart at Real Scale

CREATE TABLE AllOrders (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL,
  OrderDate DATE NOT NULL
);

WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
INSERT INTO AllOrders (OrderID, CustomerID, OrderDate)
SELECT n, (n % 500) + 1, date('2020-01-01', (n % 2000) || ' days')
FROM seq;

SELECT COUNT(*) AS TotalOrders FROM AllOrders;

TotalOrders comes back as 10,000 — a real, if modest, stand-in for the millions GreenMart is heading toward.

The Exact Same Question, at Scale

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE CustomerID = 250;

Still SCAN AllOrders — nothing about the query or the table's design changed. The plan is identical to the 5-row version; it's just now checking 10,000 rows instead of 5 to reach the same answer.

Notice that SQLite never did anything wrong here — SCAN is a perfectly valid way to answer a query, and it's often the only option when nothing tells the database a faster way exists. The problem was never the query. It's that nobody had yet given the database a shortcut.

GreenMart needs exactly that: a way to find the rows that match a condition without personally inspecting every single one. That's the entire subject of the next chapter.

Key Takeaway

A full table scan's cost grows in direct proportion to the number of rows — the same query that's invisible at 5 rows becomes a real, measurable cost once a table reaches real scale, with nothing about the SQL itself ever having changed.

Why This Matters

Almost every "why is this suddenly slow" investigation in a real production system traces back to exactly this: a query that was always doing a full scan, quietly, until the table grew large enough for that scan to actually cost something noticeable. Recognizing this pattern early is far cheaper than debugging it in production.

GreenMart finally understands why the report slowed down — the query never changed, only the amount of work behind it. The next chapter gives the database a way to skip most of that work entirely.