# D1 Cost Optimizer

> 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.

- Skill: `huifer/d1-cost-optimizer` (Agent Skill, multi-file: 21 files)
- Install (CLI): `npx skillmds@latest add huifer/d1-cost-optimizer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/huifer/d1-cost-optimizer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: huifer (https://skillmd.com/u/huifer)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/huifer/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.
```