d1-cost-optimizer

name	d1-cost-optimizer
description	Cloudflare D1 database cost optimization expert. Analyzes SQL query Row Reads consumption, identifies cost explosion risks, and provides index optimization, query rewriting, and denormalization strategies. Trigger when user mentions D1, Row Reads, query optimization, or database cost.
when_to_use	- User asks about D1 database costs - User needs to optimize SQL queries to reduce Row Reads - User encounters D1 cost overages - User needs to analyze EXPLAIN QUERY PLAN results - User needs to design D1 table structure and indexes
allowed-tools	Bash(wrangler *)

Cloudflare D1 Cost Optimization Expert

Core Principle

D1 billing is based on Row Reads (rows scanned), not rows returned. Cost explosion typically comes from uncontrolled Row Reads.

Common Anti-Patterns

Anti-Pattern	Problem	Reference
Missing indexes	Full table scans	references/unindexed-join.md
Deep OFFSET	Scans discarded rows	references/deep-pagination.md
SELECT *	Fetches unnecessary data	references/select-star-all.md
Full table scan	No WHERE clause	references/full-table-scan.md
Query in loop	N+1 problem	references/subquery-in-loop.md
LIKE '%xxx%'	Leading wildcard	references/like-wildcard.md
ORDER BY without index	Temporary sort	references/order-by-no-index.md
Function on column	Prevents index use	references/function-on-indexed-column.md
OR across columns	Cannot use index	references/or-instead-of-union.md
NOT IN subquery	NULL issues	references/not-in-subquery.md
Type mismatch	Implicit conversion	references/implicit-type-conversion.md
Multiple aggregations	Multiple scans	references/multiple-aggregations.md
COUNT(*) without index	Full scan	references/select-count-star.md
UNION vs UNION ALL	Unnecessary dedup	references/union-all-vs-union.md
COUNT queries	Expensive aggregation	references/denormalization-trigger.md
Repeated queries	No caching	references/worker-cache.md

Audit Workflow

Step 1: Analyze Current Query Row Reads

Use EXPLAIN QUERY PLAN to identify:

• SCAN TABLE = Full table scan = Cost bomb
• SEARCH TABLE USING INDEX = Using index = Good
• USE TEMP B-TREE FOR = Extra sorting = Needs optimization

Step 2: Check Missing Indexes

-- List all indexes
SELECT name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL;

-- Check foreign keys
SELECT * FROM pragma_foreign_key_list('<table_name>');

Step 3: Match Anti-Pattern

Identify which anti-pattern the query matches and apply the corresponding solution from the reference file.

Step 4: Verify Optimization

# Check query plan after optimization
wrangler d1 execute <database> --command "EXPLAIN QUERY PLAN <optimized_query>"

Benchmark Reference

Scenario	Before	After	Improvement
Unindexed JOIN	1B Row Reads	20	99.9998%
Deep OFFSET	10,020 reads	20	99.8%
Denormalized COUNT	1M reads	20	99.998%
Worker Cache hit	20 reads	0	100%
SELECT * vs specific	200KB	20KB	90%
Query in loop	10,000	1,000	90%
LIKE wildcard	100,000	500	99.5%
OR vs UNION	100,000	2	99.998%

Output Format

## D1 Cost Audit Report

### 1. Current Status
- Database: <name>
- Tables: X
- Total rows: X

### 2. Anti-Patterns Detected
| Query | Anti-Pattern | Reference | Fix |
|-------|--------------|-----------|-----|
| ... | ... | ... | ... |

### 3. Index Check
- Missing indexes: X
- Redundant indexes: X

### 4. Recommendations (by priority)
1. [P0] Fix: <anti-pattern> → Expected improvement: XX%
   Reference: references/<file>.md

### 5. Implementation
Apply fixes from referenced files.