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
