Avoiding Full Table Scans

Full-table scans are not always bad, but they can become expensive as data grows.

Your goal is to use scans intentionally, not accidentally.

Core Concepts

flowchart LR
  A[Filter query] --> B{Index matches predicate?}
  B -->|Yes| C[SEARCH with index]
  B -->|No| D[SCAN table]
  C --> E[Fewer pages read]
  D --> F[More pages read]

Common scan cause	Fix strategy
Missing index on frequent filter column	Add targeted index
Function-wrapped predicate (LOWER(col))	Normalize data or design supporting index strategy
Predicate starts from non-leading composite column	Reorder or add index matching access pattern

Code Examples

-- Setup table for scan-vs-search demonstration.
CREATE TABLE scan_demo (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  city        TEXT NOT NULL,
  amount      REAL NOT NULL
);

INSERT INTO scan_demo (customer_id, city, amount)
VALUES
  (1, 'Tokyo', 100.0),
  (2, 'Berlin', 80.0),
  (3, 'Tokyo', 40.0),
  (4, 'Paris', 75.0);

Expected output
Demo table created and rows inserted.

-- Plan before index (likely scan).
EXPLAIN QUERY PLAN
SELECT id, amount
FROM scan_demo
WHERE city = 'Tokyo';

detail
Often SCAN scan_demo without supporting index.

-- Add index and inspect plan again.
CREATE INDEX idx_scan_demo_city
ON scan_demo(city);

EXPLAIN QUERY PLAN
SELECT id, amount
FROM scan_demo
WHERE city = 'Tokyo';

detail
Often SEARCH scan_demo USING INDEX idx_scan_demo_city ....

-- Query result remains the same.
SELECT id, amount
FROM scan_demo
WHERE city = 'Tokyo'
ORDER BY id;

id	amount
1	100.0
3	40.0

SQLite-Specific Nuances

SQLite Nuance

SQLite may still choose a scan when the planner estimates it is cheaper (for tiny tables or low selectivity).

That can be a correct decision.

Common Pitfalls / Best Practices

Pitfall

Treating every SCAN as a bug and over-indexing small tables.

Best Practice

Prioritize eliminating scans only on high-frequency, high-volume query paths.

Quick Challenge

Create an index to optimize lookups by customer_id in scan_demo, then inspect plan for WHERE customer_id = 2.

View Solution

CREATE INDEX idx_scan_demo_customer
ON scan_demo(customer_id);

EXPLAIN QUERY PLAN
SELECT *
FROM scan_demo
WHERE customer_id = 2;

detail
Expected to show index SEARCH using idx_scan_demo_customer on many setups.