Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*
Yesterday’s post was all about pushing SQLite harder with Node worker threads and multiple better-sqlite3 connections. That solved the concurrency and CPU scaling problem pretty well.
But there was another bottleneck hiding in plain sight — the way we search rows.
Turns out you can squeeze a ridiculous amount of extra performance just by rethinking how you design WHERE lookups. Not by rewriting business logic. Not by switching databases. Just by changing the shape of the key you search by.
And yeah, it’s stupid how much faster it gets.
The real pain
Many apps have queries like:
SELECT * FROM table
WHERE a = ? AND b = ? AND c = ? AND d = ?;
You start with one condition, then two more get added, and suddenly you’re comparing multiple string or text fields in every lookup.
At small scale it’s fine. But the moment you scale read traffic or run queries in parallel, performance falls off a cliff because SQLite has to:
- juggle multiple values in the key comparison
- compare variable-length text
- walk deeper nodes in the B-Tree
- store bigger keys → fewer keys per page → more B-Tree levels and comparisons
So I tried something different: pack multiple identifying fields into a single hash column and index that instead.
Not rocket science — but genuinely game-changing.
The trick: hash the identifying fields into one integer
Instead of searching by (cluster, name) strings, I build a URL-like string and hash it:
export function hashUrlToKey(url: string): string {
const hash = crypto.createHash('sha256').update(url).digest();
return hash.readBigInt64BE(0).toString(); // 8 bytes → BIGINT
}
Now instead of:
WHERE cluster=? AND name=?
We do:
WHERE url_hash = ?
Create index / primary key on that column (preferably WITHOUT ROWID), and boom — SQLite only compares one 8-byte integer per step instead of multiple text values.
Benchmark: 50,000 lookups on ~52k-row table
We ran the exact same benchmark twice — once using a hashed BIGINT key lookup, and once using a composite text lookup. Nothing else changed.
Hashed lookup — url_hash (BIGINT, WITHOUT ROWID)
╔════════════════════════════════════════════════════════════════════╗
║ SVG Icon URL Lookup Benchmark ║
╚════════════════════════════════════════════════════════════════════╝
📋 Configuration:
Icon rows: 51,898
URL entries: 51,898
Total queries: 50,000 (fixed)
Worker configs: [1, 2]
CPU pinning: Workers on CPU [0, 1]
Hash algorithm: SHA256 (first 8 bytes as INTEGER)
Table structure: WITHOUT ROWID (clustered by url_hash)
⚡ Running benchmarks...
2p × 1w (2 workers, 25000 queries each) ... ✓ 0.670s
2p × 2w (4 workers, 12500 queries each) ... ✓ 0.824s
══════════════════════════════════════════════════════════════════════════════════════════════════════════════
📈 RESULTS - SVG ICON URL LOOKUP PERFORMANCE
══════════════════════════════════════════════════════════════════════════════════════════════════════════════
┌──────┬────────────┬──────────┬──────────┬──────────────┬──────────────┬─────────────┬──────────────┐
│ Rank │ Config │ Proc×Wrk │ Queries │ Duration (s) │ QPS │ Avg Query │ vs Slowest │
├──────┼────────────┼──────────┼──────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ 1 │ 2p-1w │ 2×1 │ 50,000 │ 0.670 │ 74,680 │ 13.39µs │ 1.23x │
│ 2 │ 2p-2w │ 2×2 │ 50,000 │ 0.824 │ 60,680 │ 16.48µs │ 1.00x │
└──────┴────────────┴──────────┴──────────┴──────────────┴──────────────┴─────────────┴──────────────┘
📌 Summary:
🎯 Workload: 50,000 URL lookups
⚡ Best config: 2p-1w → 0.670s
🚀 Peak QPS: 74,680.4 queries/second
⏱️ Avg lookup: 13.39µs per URL
✅ Performance validated
Composite string lookup — cluster + name (TEXT + TEXT)
╔════════════════════════════════════════════════════════════════════╗
║ SVG Icon URL Lookup Benchmark ║
╚════════════════════════════════════════════════════════════════════╝
📋 Configuration:
Icon rows: 51,898
URL entries: 51,898
Total queries: 50,000 (fixed)
Worker configs: [1, 2]
CPU pinning: Workers on CPU [0, 1]
Lookup strategy: Cluster + name equality lookup
Table structure: WITHOUT ROWID (cluster + name columns)
⚡ Running benchmarks...
2p × 1w (2 workers, 25000 queries each) ... ✓ 1.012s
2p × 2w (4 workers, 12500 queries each) ... ✓ 1.112s
══════════════════════════════════════════════════════════════════════════════════════════════════════════════
📈 RESULTS - SVG ICON URL LOOKUP PERFORMANCE
══════════════════════════════════════════════════════════════════════════════════════════════════════════════
┌──────┬────────────┬──────────┬──────────┬──────────────┬──────────────┬─────────────┬──────────────┐
│ Rank │ Config │ Proc×Wrk │ Queries │ Duration (s) │ QPS │ Avg Query │ vs Slowest │
├──────┼────────────┼──────────┼──────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ 1 │ 2p-1w │ 2×1 │ 50,000 │ 1.012 │ 49,391 │ 20.25µs │ 1.10x │
│ 2 │ 2p-2w │ 2×2 │ 50,000 │ 1.112 │ 44,975 │ 22.23µs │ 1.00x │
└──────┴────────────┴──────────┴──────────┴──────────────┴──────────────┴─────────────┴──────────────┘
📌 Summary:
🎯 Workload: 50,000 URL lookups
⚡ Best config: 2p-1w → 1.012s
🚀 Peak QPS: 49,391.36 queries/second
⏱️ Avg lookup: 20.25µs per URL
TL;DR comparison
Same machine
Same table
Same query count
Same workers
Same WAL mode
Only difference:
url_hash = one 8-byte integer
cluster+name = two text compares
Result:
BIGINT lookup is ~1.5× faster in practice
Why hashing helps
SQLite indexes are B-Trees. Search cost is O(log N), but what you compare in each step matters a lot.
| Composite text key | Hashed bigint key |
|---|---|
| multiple comparisons per row | one comparison |
| variable length | fixed 8 bytes |
| fewer keys per page | more keys per page |
| more page reads | fewer reads |
| slower CPU comparisons | single instruction compare |
It's not magic — it’s just reducing the cost per step in a structure you’re already using.
If your app already has a few conditions that act like identity fields (user_id + device_id + type, cluster + name + version, etc.), stop treating them separately. Smash them into a hash and index it.
Parallel load matters too
This improvement becomes even more obvious when running 2 processes × multiple workers hammering the DB (like in yesterday’s post).
With multi-column text lookups, workers fight over CPU more. With hashed integer lookups, they glide.
That’s the difference between:
- CPU being busy doing useful binary tree traversal
- and CPU wasting cycles comparing strings
Real takeaway (not academic BS)
This isn’t just about icons or URL paths.
If you have 3–4 WHERE equality conditions that always go together, hash them into one synthetic key and index that column.
It:
- simplifies every lookup
- shrinks index pages
- increases cache density
- dramatically speeds up read concurrency
And you don’t need to redesign your whole DB.
You just add one column and one index.
Worst case, nothing changes.
Best case, you get free performance like I just did.
Try it when:
✔ Lookups are equality-based, not ranges
✔ Multiple fields always appear together in WHERE
✔ You’re doing heavy reads or concurrency
✔ Your keys are long strings or UUIDs
Closing thought
People love arguing about SQLite vs Postgres or whatever.
Meanwhile, you can get 50%+ perf improvement inside SQLite with a single column that stores a hash.
That’s the whole point:
Databases are fast. We just give them slow keys.
👉 Check out: FreeDevTools
Any feedback or contributors are welcome!
It’s online, open-source, and ready for anyone to use.
⭐ Star it on GitHub: freedevtools
Top comments (0)