Query Optimization — Read Fewer Rows, Make Fewer Trips
Hoplink now logs every click into a clicks table, and it has grown to two million rows. The owner dashboard, which used to open instantly, now takes seconds — and the bill has grown too, because D1 charges for every row a query reads. The query did not change. The data did.
The rule
Index what you filter on; batch what you send together.
Two costs dominate a D1 query:
1. Rows scanned. Without help, SELECT COUNT(*) FROM clicks WHERE code = 'cat' must look at all two million rows to find the few hundred for cat. An index is a sorted lookup structure on a column, like the index at the back of a book, that lets SQLite jump straight to the matching rows:
-- migrations/0003_index_clicks_code.sql
CREATE INDEX idx_clicks_code ON clicks (code);You can confirm the difference with EXPLAIN QUERY PLAN (run it with wrangler d1 execute hop-db --command "..."). Before the index SQLite reports SCAN clicks — every row. After, it reports SEARCH clicks USING INDEX idx_clicks_code (code=?). The meta.rows_read value on each result tells the same story in numbers.
Indexes are not free: each one takes storage and makes every INSERT a little slower, because the index must be updated too. Index the columns you actually filter, join or sort on — not every column.
2. Round trips. Your Worker and the database may sit in different cities, so each query is a network hop. Sending five queries one after another pays that hop five times. env.DB.batch() sends several prepared statements in one trip, and runs them as a single transaction — all succeed or none do:
const [total, today, top] = await env.DB.batch([
env.DB.prepare("SELECT COUNT(*) AS n FROM clicks WHERE code = ?").bind("cat"),
env.DB.prepare("SELECT COUNT(*) AS n FROM clicks WHERE code = ? AND clicked_at >= date('now')").bind("cat"),
env.DB.prepare("SELECT country, COUNT(*) AS n FROM clicks WHERE code = ? GROUP BY country ORDER BY n DESC LIMIT 3").bind("cat"),
]);Smaller wins stack on top: select only the columns you need instead of SELECT *, and use LIMIT for pages.
The shortcut
For any slow query, read its WHERE, JOIN and ORDER BY clauses and ask "Is there an index that starts with these columns?" Then look at the handler and ask "How many awaits hit env.DB?" — more than one independent query is a batch waiting to happen.
Find the filter
Every dashboard query says
WHERE code = ?. No index coverscode.
Trap: adding an index on a column you only ever display. It costs space and slows writes while speeding up nothing.
Drills
Answer fast — trust the rule.
EXPLAIN QUERY PLAN reports SCAN clicks for a query filtered by code. What does that suggest?
What is a real cost of adding an index?
What does env.DB.batch([...]) give you?
Which column most deserves an index on Hoplink's clicks table?
Remember
- Index what you filter on; batch what you send together.
- Shortcut: check WHERE, JOIN and ORDER BY columns for an index, then count the awaits.
- Trap: indexing display-only columns, and one round trip per query.