Query Optimization

Performance tuning starts with one rule: optimize real slow queries, not guesses.

In SQLite, a small number of targeted changes can deliver large speedups.

Core Concepts

flowchart LR
  A[Identify slow query] --> B[Inspect plan]
  B --> C[Adjust schema/query]
  C --> D[Re-check plan]
  D --> E[Measure again]

Optimization lever	Typical impact
Better filtering predicates	Fewer rows processed
Proper indexes	Faster lookups/joins
Smaller selected column set	Less data movement
Updated statistics (ANALYZE)	Better planner choices

Before Optimization

-- Query pattern without supporting index.
SELECT order_id, customer_id, total
FROM perf_orders
WHERE customer_id = 42;

Expected output
Correct rows return, but may require scanning many rows on large tables.

After Optimization

-- Add targeted index for frequent lookup.
CREATE INDEX idx_perf_orders_customer
ON perf_orders(customer_id);

Expected output
Index created, enabling faster customer-based lookups.

Code Examples

-- Setup table for optimization examples.
CREATE TABLE perf_orders (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  status       TEXT NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO perf_orders (customer_id, status, total)
VALUES
  (42, 'paid', 99.0),
  (42, 'pending', 40.0),
  (7,  'paid', 20.0),
  (9,  'cancelled', 10.0);

Expected output
Table created and sample rows inserted.

-- Optimized lookup query.
SELECT order_id, status, total
FROM perf_orders
WHERE customer_id = 42
ORDER BY order_id;

order_id	status	total
1	paid	99.0
2	pending	40.0

-- Refresh planner stats after structural changes.
ANALYZE perf_orders;

Expected output
Planner statistics updated for perf_orders.

SQLite-Specific Nuances

SQLite Nuance

SQLite's planner is cost-based and uses available statistics/indexes to choose access paths.

Optimization is iterative: inspect plan, adjust, validate.

Common Pitfalls / Best Practices

Pitfall

Adding many indexes without verifying workload fit, then slowing down writes.

Best Practice

Optimize one hot query at a time and keep only indexes that clearly improve real usage.

Quick Challenge

You frequently run WHERE status = 'paid' on perf_orders. Add a targeted index.

View Solution

CREATE INDEX idx_perf_orders_status
ON perf_orders(status);

Expected output
Status-based filtering can be faster on large datasets.