nlqdb

Solve · Backend engineers

How do I reduce rows read in Cloudflare D1?

Run wrangler d1 insights <db> --sort-by=reads, then EXPLAIN QUERY PLAN the top query: a SCAN of a table it filters is the rows you pay for. CREATE INDEX on the filter's columns, then PRAGMA optimize. Or run nlq attach --database-id: nlqdb finds those scans, proposes each index and measures rows read after.

D1 bills every row a query reads, not the rows it returns, so one unindexed lookup on a 5,000-row table costs 5,000 reads a call. Query insights rank the heavy texts, but none says which index would fix them, and none tells you days later whether the index you added cut the reads or only added writes.

The command that solves it.

What nlqdb does for this

  • nlq attach --database-id reads the catalog and proposes an index for every foreign key no index leads with — seconds after attach.
  • It runs EXPLAIN QUERY PLAN on each heavy text in 24 h of query insights that returns at most 5 % of its rows read.
  • A SCAN filtered by equality becomes one CREATE INDEX proposal with its DROP INDEX inverse; nlq apply <id> builds it and runs PRAGMA optimize.
  • 72 hours later it compares rows read per run and mean time; either over 10 % worse runs the inverse, with no action from you.

Run in your terminal

curl -fsSL https://nlqdb.com/install | sh
export NLQDB_API_KEY=sk_live_... # from nlqdb.com/app/keys
export CLOUDFLARE_API_TOKEN=... # D1 Read + Account Analytics Read; D1 Edit to apply
nlq attach --database-id <uuid> --account-id <id>

Attach reads the catalog, so unindexed foreign keys come back as proposals in seconds; the heavy scans in your query insights follow within the hour.

Or attach in the browser →

What this replaces

What nlqdb doesn't try to do here

  • No p95 — D1's analytics keep no percentiles, so nlqdb judges an index on rows read per run and mean time, never a latency percentile.
  • No fix for an aggregate or a COUNT(*) over a whole table — an index can't make those read fewer rows, so nlqdb proposes nothing for them.
  • No anonymous attach — the Cloudflare token is a credential, so attach needs a signed-in account; it is sealed server-side and never printed back.

Questions buyers ask

How do I reduce rows read in Cloudflare D1?
Find the heavy queries with wrangler d1 insights <db> --sort-by=reads or each result's meta.rows_read. EXPLAIN QUERY PLAN the worst: SCAN reads the whole table, SEARCH ... USING INDEX reads only matching rows. Index the columns the scanned query filters on, run PRAGMA optimize, and re-read its rows read per call.
Does adding an index in D1 cost rows written?
Yes. Every insert, and every update that touches an indexed column, also writes the index, so an index trades rows written for rows read. It pays on a table read far more than written — which is why nlqdb's verify also compares rows read per run after the apply and reverts an index that made things worse.
Does nlqdb run my queries or change my D1 database to find indexes?
No. It reads the catalog and D1's query insights, and plans texts with EXPLAIN QUERY PLAN without executing them. Nothing changes until you apply a proposal, and every proposal carries the DROP INDEX that undoes it.
What does an applied D1 index look like?
On a test D1, a lookup by user_id with no index read 5,000 rows a call; after nlqdb applied its proposed index it read 51. On a second, a lookup by title went from 4,999 rows read per run to 1, and the verify job kept the index with no caller.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Attach your database

Sign in, hand nlqdb a Postgres connection string or a D1's account id, database id and API token, and see the first proposals. Nothing runs on your database until you apply one.

Attach a database →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.