Writing fast queries

Sargable predicates

A condition is sargable (from "search argument able") when the database can answer it by searching an index. The rule of thumb: leave the indexed column alone on one side of the comparison. As soon as you wrap the column in a function or do maths on it, the index (which stores the plain values) cannot be searched.

-- With an index on orders(order_date), real SQLite plans:
WHERE strftime('%Y-%m', order_date) = '2025-03'
  SCAN orders USING COVERING INDEX idx_orders_date
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'
  SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?)

Notice the half-open range: >= the first day and < the first day of the next month. It works for dates and timestamps alike, and never misses the last day's evening.

LIKE with a leading wildcard

An index on name is sorted from the first letter, so LIKE 'Rice%' can (in many engines) jump to the R section. LIKE '%Rice%' could match anywhere in the text, so every value must be checked. For "contains" search on big tables, use full-text search (SQLite FTS5, PostgreSQL tsvector or trigram indexes, MySQL FULLTEXT) or a search engine.

Select only what you need

SELECT * drags every column, including long text, across the disk and network. Naming only the needed columns also makes covering indexes possible: SELECT id, order_date can be served from an index on (customer_id, order_date) alone, while SELECT * always visits the table.

The N+1 query problem

This is the most common performance bug in app code. The app runs one query to get a list, then one more query for each item in the list. With 50 customers that is 51 round trips to the database; with 5,000 it is 5,001, each paying network delay.

customers = db.execute("SELECT id, name FROM customers").fetchall()     # 1 query
for cid, name in customers:
    n = db.execute("SELECT COUNT(*) FROM orders WHERE customer_id = ?", (cid,)).fetchone()[0]   # + N queries
    print(name, n)

SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Pagination: keyset instead of a big OFFSET

LIMIT 20 OFFSET 100000 looks cheap, but the database must produce and throw away 1,00,000 rows to give you 20, and page 5,000 is slower than page 1. Keyset pagination (also called seek pagination) remembers where the last page ended and asks for rows after it. With an index on the sort columns, every page is equally fast.

-- Page 1
SELECT id, order_date, status FROM orders ORDER BY order_date, id LIMIT 3;
-- The last row was (2025-02-20, 104). Page 2:
SELECT id, order_date, status FROM orders
WHERE (order_date, id) > ('2025-02-20', 104)
ORDER BY order_date, id LIMIT 3;
-- 105 | 2025-03-02 | Shipped
-- 106 | 2025-03-15 | Delivered
-- 107 | 2025-03-28 | Placed

The trade-off: keyset pages cannot jump straight to "page 37". That suits infinite scroll and "Next" buttons, which is what most apps use.

EXISTS instead of COUNT(*) > 0

To ask "does this customer have any delivered order?", COUNT(*) > 0 counts every match, while EXISTS can stop at the first one. EXISTS also states the intent clearly.

Batch your writes

Every separate statement outside a transaction is its own transaction, and committing usually waits for the disk. Inserting 10,000 rows one by one can take far longer than inserting them inside one BEGIN ... COMMIT, or with multi-row INSERT ... VALUES (...), (...). Batch in chunks (for example 1,000 rows) so one transaction does not grow too large.

Keep statistics fresh

The planner chooses plans using statistics: how many rows a table has and how values are spread. ANALYZE collects them (SQLite stores them in sqlite_stat1; the SQLite docs recommend running PRAGMA optimize regularly). PostgreSQL's autovacuum runs ANALYZE automatically, but run it by hand after a big load. MySQL uses ANALYZE TABLE.

Common mistakes

• Wrapping an indexed date in a function. Fix: a half-open range on the bare column.
• Fetching related rows in a loop. Fix: a JOIN, or one query with WHERE id IN (...).
• Deep OFFSET pages on big tables. Fix: keyset pagination on an indexed sort key plus a unique tie-breaker.
• Optimising before measuring. Fix: find the slowest frequent queries first (slow query logs, pg_stat_statements in PostgreSQL), then read their plans.