Hello, I'm Maneshwar. I'm working onFreeDevTools 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 multiplebetter-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 designWHERE 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*FROMtableWHEREa=?ANDb=?ANDc=?ANDd=?;

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 indexthat 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:

exportfunctionhashUrlToKey(url:string):string{consthash=crypto.createHash('sha256').update(url).digest();returnhash.readBigInt64BE(0).toString();// 8 bytes → BIGINT}

Now instead of:

WHEREcluster=?ANDname=?

We do:

WHEREurl_hash=?

Create index / primary key on that column (preferablyWITHOUT 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 isO(log N), but what you comparein each step matters a lot.

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 addone column andone index.

Worst case, nothing changes.
Best case, you get free performance likeI 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 get50%+ 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