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

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.