SQLite: Avoid Comparing Against Expressions

Hosted ondailyplanet.iovia theHypermedia Protocol

Seed Team

Tech Talks

Stories

Human Interface Library

Human Interface Library

/Tech Talks/SQLite: Avoid Comparing Against Expressions

SQLite: Avoid Comparing Against Expressions

Alex Burdiyan

24 November 2025, 10:18

In our backend code I've seen a bunch of places where we compare the result of an expression with an input parameter/s.

For example:

SELECT
  id
FROM public_keys
WHERE hex(principal) = :principal LIMIT 1;

SELECT DISTINCT
  multihash,
  codec
FROM blobs
WHERE hex(multihash) IN (
  SELECT value from json_each(:mhash_json)
)
AND size > 0

In both of these queries the WHERE clause can't use an index, and will do a full table scan. This is because the index doesn't know the result of the hex() expression, so it can't compare the column value against the input parameter.

The solution for the first query is to do WHERE principal = unhex(:principal) and for the second one is to do WHERE multihash IN (SELECT unhex(value) FROM json_each(:mhash_json).

So, instead of running an expression on all the rows in the table, we run the "reverse" expression to convert the input parameter to the same value as the column, which will let us use the index.