{"slug":"sql-query-optimizer","name":"SQL Query Optimizer for D1, SQLite and Turso","version":"1.0.1","updated_at":"2026-10-04T10:21:01.732Z","use_when":"Reviews SQL queries, schemas and migrations for SQLite, Cloudflare D1 and Turso, where the bill and the speed follow the rows a query READS, not the rows it returns. Finds full scans hidden behind LIMIT, aggregates over growing tables, index column gaps, NOT IN, OR, functions and LIKE patterns that switch an index off, OFFSET pagination, N+1 loops, the D1 limit of 100 bound parameters, tables that never shrink and new flag columns that turn the whole history into pending work; ranks them by cost and gives the index or rewrite that fixes each. Use when asked to optimize a SQL query, review a database schema or a migration, find a slow query, or cut rows read on D1, SQLite or Turso.","not_for":"PostgreSQL or MySQL tuning, where the planner and the costs differ, though many patterns carry over; running or timing your queries; choosing a database. It reads the SQL you paste, so anything that depends on data it was not shown, such as real table sizes, is stated as an assumption.","languages":["en","bg"],"tags":["sql","query-optimization","sqlite","cloudflare-d1","turso","database-indexes","rows-read"],"category":"code","category_url":"https://aiskills402.com/categories/code","keywords":["optimize a SQL query","review a database schema","find a slow query","cut rows read"],"faq":[{"q":"Does it work for PostgreSQL?","a":"It is written for SQLite and the services built on it, Cloudflare D1 and Turso, where the bill follows the rows a query reads. Several patterns, such as an index column gap or a function wrapped around an indexed column, hurt in PostgreSQL too, but its planner differs, so treat the advice there as a lead and check it with EXPLAIN."},{"q":"How do I know a suggested fix really helps?","a":"Each report ends with a way to check: run EXPLAIN QUERY PLAN against a local SQLite copy of the schema and look for SEARCH instead of SCAN, and on D1 compare meta.rows_read before and after. We confirmed every planted problem and every accepted fix in our tests the same way."},{"q":"Will it invent problems in healthy SQL?","a":"Claude Sonnet answered 'No findings.' for our clean schema. Claude Haiku invented a finding there and suggested a DELETE with LIMIT that standard SQLite rejects, so with Haiku, run each suggested statement before relying on it."},{"q":"What is the D1 parameter finding about?","a":"D1 rejects a statement with more than 100 bound parameters. An id list built from your data, or a multi-row insert, uses one parameter per value, so code that worked at launch fails on its own when a reader saves the 101st item. The skill points to such places and suggests chunks of 50."}],"examples":[{"lang":"en","model":"claude-sonnet-5-5","input_excerpt":"Schema:\nCREATE TABLE events (id INTEGER PRIMARY KEY, scope TEXT NOT NULL, symbol TEXT NOT NULL, created_at INTEGER NOT NULL, payload TEXT);\nCREATE INDEX idx_events ON events(scope, symbol, created_at);\n\nThe table has about 2 million rows; 90% of them have scope = 'market'. Old rows are deleted after 30 days.\n\nRuns every 30 seconds:\nSELECT * FROM events WHERE scope = 'market' AND created_at > ?…","output_excerpt":"[INDEX-GAP] `SELECT * FROM events WHERE scope = 'market' AND created_at > ? ORDER BY created_at DESC LIMIT 50`\nReads: the index `(scope, symbol, created_at)` is searched by `scope` only, because `symbol` has no condition. That means every `market` row still inside the 30-day window is read (about 1.8M at 90% of 2M), then sorted in a temp B-tree before LIMIT 50 applies.…"},{"lang":"bg","model":"claude-sonnet-5-5","input_excerpt":"Schema:\nCREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT NOT NULL, run_at INTEGER NOT NULL, payload TEXT);\n\nThe table keeps every job ever run (about 300,000 rows, 'done' or 'failed'). New jobs arrive a few times a day.\n\nA worker polls every minute; most polls find nothing:\nSELECT * FROM jobs WHERE status = 'pending' AND run_at <= ? ORDER BY run_at LIMIT 90;","output_excerpt":"**[EMPTY-PATH]** `SELECT * FROM jobs WHERE status = 'pending' AND run_at <= ? ORDER BY run_at LIMIT 90`\nReads: цялата таблица `jobs` (~300 000 реда) при всяко пускане на worker-а. Заявката няма индекс освен първичния ключ. Когато няма чакащи задачи, LIMIT никога не се достига и заявката прочита всички редове.…"}],"page_url":"https://aiskills402.com/skills/sql-query-optimizer","markdown_url":"https://aiskills402.com/skills/sql-query-optimizer.md","image_url":"https://cdn.aiskills402.com/og/skills/sql-query-optimizer/6652a92f.png","related_url":"https://api.aiskills402.com/v1/skills/sql-query-optimizer/related","purchases_count":null,"tested":{"date":"2026-10-03","strong":{"model":"claude-sonnet-5-5 (Claude Code alias \"sonnet\")","verdict":"Named the planted problem with the right code in all 11 cases that had one and answered 'No findings.' for the clean schema. Its fixes were working SQL: an index on (scope, created_at) for the gap, a partial index on status = 'pending' for the empty poll, a range on the raw timestamp, keyset pagination, chunks of 50 for D1's 100-parameter limit, a backfill in the same migration, the partial index's WHERE copied into ON CONFLICT. At first 2 of 12 were scored as failures because our pattern accepted only one of the two fixes the skill names; Sonnet chose the other (a latest-prices table kept by the writer; day bounds computed in SQL), both were confirmed with EXPLAIN QUERY PLAN, and the same answers rescored at 12 of 12."},"weak":{"model":"claude-haiku-4-5-20251001 (Claude Code alias \"haiku\")","verdict":"Found the planted problem with the right code and a working fix in all 11 cases that had one, including the Bulgarian request. On the clean schema it invented a finding under a code of its own and proposed DELETE ... LIMIT, which standard SQLite rejects as a syntax error. With Haiku, run any suggested statement before relying on it."},"note":"Twelve fictional schemas and queries: eleven with one planted problem each (aggregate scan, index gap, NOT IN, OR on a flag, empty queue poll, function on a column, prefix LIKE, OFFSET with COUNT, D1 parameter limit, flag column without backfill, ON CONFLICT against a partial index) and one clean; one request in Bulgarian. Every planted plan and every accepted fix was confirmed with EXPLAIN QUERY PLAN on SQLite 3.53 without ANALYZE. Machine checks look for the finding code and a working fix; one run per model and case. The rescoring of stored answers after two patterns were widened is in results-2026-10-03-recheck.md.","baseline":{"date":"2026-10-03","rows":[{"label":"Cases passed","better":"higher","strong":{"with":{"n":12,"of":12},"without":{"n":10,"of":12}},"weak":{"with":{"n":11,"of":12},"without":{"n":3,"of":12}}}],"note":"One run per model and case. Without the skill Sonnet missed an OR that switches the index off and a function wrapped around an indexed column. Haiku missed nine, among them the D1 limit of 100 bound parameters, a LIKE prefix that scans, and a new flag column that turns the whole history into pending work. On the clean schema Haiku failed with and without the skill: with it, it invented a finding."},"report_url":null},"price_usd":"0.01","price_micro":10000,"size_bytes":10077,"sha256":"09a9cd1326ed96725c5c9da3fe6c5909ff35ccacd113a0a60939bc6b0ba874f5","outline":["Hard rules","Finding codes","Report format","Work in this order","Short example"],"license":{"summary":"Perpetual, non-exclusive; use and modify for yourself incl. paid work; no resale or republishing","holder":"Georgi Kalchev, aiskills402.com","url":"https://aiskills402.com/docs#license"},"buy_url":"https://api.aiskills402.com/v1/skills/sql-query-optimizer/file","redownload_url_template":"https://api.aiskills402.com/v1/purchases/{token}","mcp_tool":null,"payment":{"protocol":"x402","scheme":"exact","asset":"USDC","selling":true,"network":"base","network_caip2":"eip155:8453","pay_to":"0x8e37022edcf0f21cf3c9f93fee9d4d32519f36f4","facilitator":"cdp"},"seo_title":"SQL Query Optimizer for D1, SQLite and Turso","seo_description":"Skill file that reviews SQL, schemas and migrations for D1, SQLite and Turso, finds what inflates rows read, and ranks fixes by cost. $0.01 once.","versions":[{"version":"1.0.1","date":"2026-10-04","changelog":"# Changelog\n\n## 1.0.1 — 2026-10-04\n\n- Test summary: the same cases with and without the skill, per model, now shown next to the verdicts, including where the skill made no difference. The skill file itself is unchanged.\n\n## 1.0.0 — 2026-10-03\n\nFirst release: reviews SQL, schemas and migrations for SQLite, Cloudflare D1 and Turso by rows read; fifteen finding codes, each with a concrete index or rewrite, ranked by cost, with a way to verify the fix.\n"},{"version":"1.0.0","date":"2026-10-03","changelog":"# Changelog\n\n## 1.0.0 — 2026-10-03\n\nFirst release: reviews SQL, schemas and migrations for SQLite, Cloudflare D1 and Turso by rows read; fifteen finding codes, each with a concrete index or rewrite, ranked by cost, with a way to verify the fix.\n"}]}