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). The former allows you to get what you want, but as you wrote, you must rewrite your queries. The latter, if supported, would allow to move the "function definition" to the column, and index the vcolumn directly. Of course, if you have a new column, you have to change your queries anyway, but at least you'd store the substring only once, in the index, and no need to manually insert that substring (or via a trigger, which are best avoided, especially like so to store denormal data). Many argue views can do the same as virtual columns, but they are in fact much more convenient IMHO, and allow to have a single table instead of a table/view combo. FWIW. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
