Re: [sqlite] Reducing index size

> Maybe you're writing for a fixed-space embedded device, which nonetheless > has space for the gigabytes required Actually I am at the other end of the spectrum and running out of SSD space on the server-side, with the higher SSD storage tiers being quite more expensive. I am also leaving the re

Re: [sqlite] Reducing index size

There's a good chance this comment won't be useful to you, Eric. Nevertheless, Any chance of relaxing your space requirement? I.e., what bad things happen if the space is not reduced? Maybe you're writing for a fixed-space embedded device, which nonetheless has space for the gigabytes required,

Re: [sqlite] Reducing index size

On 30 July 2018 at 18:10, Eric Grange wrote: > @Rowan Worth > > Doesn't that problem already exist with the current index? Except worse > > because it's storing the cryptographic hash *and* the rowid. > > No, because SQLite is using a B-Tree (and with cryptographic hashes, it > should even take l

Re: [sqlite] Reducing index size

@Rowan Worth > Doesn't that problem already exist with the current index? Except worse > because it's storing the cryptographic hash *and* the rowid. No, because SQLite is using a B-Tree (and with cryptographic hashes, it should even take less effort to balance) On Mon, Jul 30, 2018 at 12:05 PM

Re: [sqlite] Reducing index size

On 30 July 2018 at 17:53, Eric Grange wrote: > @Rowan Worth > > 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? > > As I have millions of rows, and data could get inserted anywhere in t

Re: [sqlite] Reducing index size

@Dominique Devienne > SQLite supports function-based indexes, but unfortunately if does not support "function-based columns". Far fetched maybe, but could a virtual table or table-valued functions be used to provide that? ie. use the virtual table to pass data directly to an index, and then expos

Re: [sqlite] Reducing index size

On 30 Jul 2018, at 10:25am, Dominique Devienne wrote: > 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. It's the usual speed v

Re: [sqlite] Reducing index size

On 30 July 2018 at 17:25, Dominique Devienne wrote: > On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin > wrote: > > > On 30 Jul 2018, at 9:32am, Eric Grange wrote: > > > > > As these are cryptographic GUIDs, the first few bytes of a values are > in > > > practice unique, so in theory I can index j

Re: [sqlite] Reducing index size

@Simon Slavin > Don't index using substr(). That would be slow because it has to keep working out substr(). I gave it a try, but that grows the size of the tables, and would require a full update of the key/value table, so not something that can be deployed without a lot of I/O. The substr() sho

Re: [sqlite] Reducing index size

On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin wrote: > On 30 Jul 2018, at 9:32am, Eric Grange 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

Re: [sqlite] Reducing index size

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 On 30 July 2018 at 09:32, Eric Grange wrote: > Hi, > > Is there a way to reduce the s

Re: [sqlite] Reducing index size

On 30 Jul 2018, at 9:32am, Eric Grange 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 qu

[sqlite] Reducing index size

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 "co