SQLite FTS5 Full-Text Search Optimization: BM25 Ranking Calibration, Trigram Tokenization, and D1 Performance

## Architecting Zero-Penalty Search on Serverless SQLite

Cloudflare D1 charges based on rows read and written rather than CPU execution seconds. Consequently, poorly structured search queries that scan large tables produce extreme billing penalties. SQLite's native FTS5 (Full-Text Search 5) extension provides inverted indexing with BM25 ranking directly in serverless SQLite.

### 1. FTS5 Indexing Strategy Comparison

| Search Architecture | Rows Touched (100k Rows) | Query Latency (P95) | Typo Tolerance | Index Size Overhead |
|:---------------------|:------------------------:|:-------------------:|:--------------:|:-------------------:|
| Unindexed LIKE '%q%' | 100,000 | 480ms | None | 0% |
| B-Tree Prefix (q%) | 24 | 18ms | None | 15% |
| FTS5 Porter Stemmer | 12 | 4ms | Stemming Only | 35% |
| FTS5 Trigram Tokenizer | 32 | 9ms | Substring & Typo | 75% |

### 2. Calibrating BM25 Relevance Scoring

The BM25 scoring function evaluates matching document $D$ for query terms $q_i$:

$$\text{Score}(D, Q) = \sum_{i=1}^n \text{IDF}(q_i) \cdot \frac{f(q_i, D) \cdot (k_1 + 1)}{f(q_i, D) + k_1 \cdot (1 - b + b \cdot \frac{|D|}{\text{avgdl}})}$$

In FTS5, column weight multipliers allow prioritizing title matches over body text:

```sql
SELECT rowid, rank
FROM search_index
WHERE search_index MATCH 'cloudflare worker*'
ORDER BY bm25(search_index, 10.0, 1.0) ASC
LIMIT 20;
```