If I understand correctly then changing from a base64 index to a blob containing the raw bytes would save 25%
Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 30 July 2018 at 09:32, Eric Grange <zar...@gmail.com> wrote: > Hi, > > Is there a way to reduce the size of an index on strings/blobs ? > > I have tables which are a key + value, the key is an "integer primary key > autoincrement", which is used for references in all other tables of the > schema. > The values are cryptographic GUIDs (so 256 to 512 bits in size) with a > "compact" encoding (either base64 or blob rather than hexadecimal strings), > but they still represent gigabytes of data. > > Those tables have an index on the value, and my problem is that the size of > this index (as reported by dbstat or sql3_analyzer) is about the same > as the table. > > 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. > > Before starting down that route, is there another way? > > My searches on those indexes are by either exact value or by value start > (human search & auto-completion) > > Eric > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
