using less memory to lookup IP addresses in Mess With DNS https://jvns.ca/blog/2024/10/27/asn-ip-address-memory/

Julia Evans

Simon Willison

@b0rk "SQLite doesn’t have support for big integers and IPv6 addresses are 128 bits, so I decided to store them as text." - might be worth trying a BLOB column, that way you can store those 128 bits directly

Julia Evans

@simon oh I thought you couldn't compare/create indexes on BLOBs so I didn't try

Simon Willison

@b0rk indexes work on BLOBs - you can even use them as a primary key for things like binary UUIDs

This trick appears to work to get SQLite to use an index on the age column and an index on the city column at the same time (I ran a quick experiment just now):

SELECT id, name FROM people WHERE age > 25
INTERSECT
SELECT id, name FROM people WHERE city = "New York";

Explain says:

Julia Evans

@simon ty, edited the post to mention that BLOB is probably a better choice!

do you know if `INTERSECT` needs to materialize the results of the two queries in memory?

I tried that too but the “INTERSECT USING TEMP B-TREE” made me think that it was actually making two separate queries, saving all the results, and then intersecting them.

sqlite's `.timer` seemed to suggest that the `INTERSECT` version was slower as well though I didn't benchmark it that carefully

Simon Willison

@b0rk I don't know for sure, but yeah it seems likely that intersect works like that

I wonder if there are some wildly clever tricks that can recur your problem to an indexed prefix like query or similar? Some kind of IP address equivalent of the geohash trick perhaps

Simon Willison

SQLite has RTree indexes as a compile-time option, I don't know much about them but maybe there's a trick you could do with those