On 30 Jul 2018, at 9:32am, Eric Grange <zar...@gmail.com> wrote: > As these are cryptographic GUIDs, the first few bytes of a values are in > practice unique, so in theory I can index just the first few bytes (using > substr()), > this indeed reduces in a much smaller index, but this also requires > adapting all queries that search by value.
Don;t index using substr(). That would be slow because it has to keep working out substr(). Instead create another column in the table called "hash" which contains the first few bbytes, and index that column instead of the full-length one. If you define it hash BLOB UNIQUE then SQLite will make up and maintain its own index on the column, which means you don't have to. And it will check for uniqueness in case your assumption is wrong. How you set that new column's value ... it could be done by modifying the INSERT. Or with a TRIGGER. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users