On 30 July 2018 at 17:25, Dominique Devienne <ddevie...@gmail.com> wrote:
> On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin <slav...@bigfraud.org> > wrote: > > > 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. > > > But that's the rub IMHO. You're still storing that substring info, twice, > once in the table, another in the index. > SQLite supports function-based indexes, but unfortunately if does not > support "function-based columns". > (alas called virtual columns in Oracle, or Computed columns in SQL server I > believe. Not (yet) in Postgres). > What if you could create a "lite" index, which stores just the rowids in a particular order and refers back to the table for the rest of the column data? The ordering it provides would allows you to binary search as usual. But the worst case might be too expensive (ie. if pages are thrown out of cache before being re-used), especially as it would have to be paid every time you lookup the index... -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
