d1-cost-optimizer

# 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 ```sql -- 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 ```bash # 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. ```