huifer 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 bombSEARCH TABLE USING INDEX= Using index = GoodUSE 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.