rf on Nostr: npub1upkp7…4tcwy Binsearch in RAM will still easily beat SQLite, but a trick: ...

npub1upkp7fd7rc3lrjg23r8gy0wc723vze7mxlx5984ut6zurjzpf5xss4tcwy (npub1upk…tcwy) Binsearch in RAM will still easily beat SQLite, but a trick:

SQLite doesn't know that the start_ips and end_ips form non-overlapping ranges. When it could just check the one row with the highest start_ip <= ip, it checks more. You can write a query that gets it to check that one row (both ?'s are the same IP):

select * from (select * from ipv6_ranges where start_ip <= ? order by start_ip desc limit 1) t where end_ip >= ?;

Code from playing w/it: https://pastebin.com/JmgfbyHd