Slow Queries in D1: How to Diagnose and Optimize

SQLite has a reputation for being a simple database that "works and that's it." This reputation is deserved for cases where it is used as an embedded database in desktop and mobile applications — where the data set is small and the query planner has an easy job. In D1, this context changes: tables with hundreds of thousands of rows, multiple queries per user request, and a pricing model that charges for each row that the bank reads during the execution of a query, not for each row returned to the application. A poorly optimized query in production is not just slow — it's expensive, and the cost increases linearly with the volume of data.

EXPLAIN QUERY PLAN: diagnosis before optimization

Before creating any index or rewriting any query, run EXPLAIN QUERY PLAN against the problematic query. On D1, you can do this remotely via wrangler d1 execute NOME_DO_BANCO --remote --command "EXPLAIN QUERY PLAN SELECT ...", or locally with sqlite3 pointing to file .wrangler/state/v3/d1/.

The output is a list of operations that the query planner will perform. "SCAN TABLE orders" is the warning signal: it means that the database will scan all the rows in the table. "SEARCH orders USING INDEX idx_orders_user_id (user_id=?)" is what you want to see: the database uses the index to directly find the relevant rows. "SEARCH orders USING INDEX idx_orders_user_date (user_id=?AND date>?)" indicates that a composite index is being leveraged for both the equality filter and the range filter.

The most common mistake is creating indexes after problems appear in production. The EXPLAIN QUERY PLAN should be part of the development process — run against every query that touches tables with more than a few thousand rows before the first deployment. The cost of an unnecessary index is storage space. The cost of a query without an index in production is measured in money.

The patterns that generate table scan in D1

Four recurring situations cause full table scan. The first is the most obvious: absence of index on the column used in WHERE. SELECT * FROM orders WHERE user_id = ? without an index on user_id reads all rows in the table to find the user's rows. The correction is straightforward: CREATE INDEX idx_orders_user_id ON orders(user_id).

The second situation is the combination of WHERE with ORDER BY not covered by a composite index. SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC can use the index on user_id to filter, but then it needs to sort the result in memory — an operation called filesort. A composite index on (user_id, created_at) eliminates filesort because the data is already sorted by created_at within each user_id.

The third is LIKE with an initial wildcard. WHERE title LIKE '%termo%' cannot use any index: the wildcard at the beginning of the string prevents the database from using the index ordering to discard lines. For two-sided wildcard text search, use FTS5 virtual tables: CREATE VIRTUAL TABLE posts_fts USING fts5(title, content, content=posts). FTS5 queries with MATCH are indexed and scalable.

The fourth is type coercion. SQLite uses type affinity — a column defined as TEXT can store integers, and a WHERE id = 42 comparison against a TEXT column may not use the index depending on how the values were entered. Maintaining type consistency between the schema, inserted values and queries is more important in SQLite than in databases with strict typing.

N+1 in D1 and the role of db.batch()

The N+1 problem has an extra dimension in D1: each query is a subrequest, and subrequests have a limit of 1000 per Worker invocation. An endpoint that searches for 50 requests and then does a SELECT for the items in each request individually executes 51 queries — 51 subrequests, at the cost of 51 round-trips to the database, each one adding network latency to the total response time.

db.batch() solves this by grouping multiple queries into a single subrequest. All queries in the batch are executed in a single roundtrip to the database. The result is an array with one element per query, in the same order in which they were sent. For the orders and items pattern, the batch contains: the orders query and a query with IN covering all order IDs. Two subrequests in total, regardless of how many requests are returned.

ORMs that support D1 — such as Drizzle ORM, which has native integration — have eager loading options that automatically build queries with JOIN or batch instead of N+1. The default behavior of most ORMs, however, generates N+1 unless you explicitly configure eager loading. Checking the SQL generated with console.log in the development environment before going to production is the most direct way to identify these patterns.

The real cost of not having an index: an example with numbers

A D1 bank with 200 thousand orders. The query SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20 without index in status performs a full table scan: 200 thousand rows read, 20 rows returned. At $0.001 per million reads, each execution of this query costs $0.0002.

With 100,000 daily executions of this endpoint — common for a panel that updates via polling or an operations dashboard — the cost is $20 per day, $600 per month, just for this query. The composite index CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC) completely changes the plan: the database reads only the records with status = 'pending' using the index, already ordered by created_at. Assuming 5 thousand pending requests, the query reads 5 thousand rows, returns 20, and costs $0.000005 per execution. At $100k runs/day, the cost drops to $0.50 per day, $15 per month.

The index itself takes up approximately 5-10MB for 200K rows. At $0.75/GB-month, this storage costs less than $0.01 per month. The difference between $600/month and $15/month in reading costs, for a fraction of a penny investment in storage, is the kind of optimization that should never be put off until "after the traffic grows" — because when the traffic grows, the cost will already be incurred.

Slow Queries in D1: How to Diagnose and Optimize

EXPLAIN QUERY PLAN: diagnosis before optimization

The patterns that generate table scan in D1

N+1 in D1 and the role of db.batch()

The real cost of not having an index: an example with numbers

Also read