Hi Edward, To be specific about my case:
What I really wanted is to have Key -> Value mapping (B-tree). I love how SQLite can handle compound keys. So for example, if my key is a compound key (A, B), I can query values by A as well as by (A, B). This is very useful. Imagine a case when Key is let's say a string of fixes length of 32 bytes. The value on the other hand is just an unique id, a 8 byte INTEGER. This is special case, when key part is much bigger than actual data. So, if table looks like this, CREATE TABLE index(Key TEXT, Value INTEGER); behind the curtain, SQLite will store ROWID + Key + Value = 8 + 32 + 8 = 48 bytes of data per row. This is how I create index: CREATE INDEX key_idx ON index(Key); I am strongly sure that behind the curtain, each record in the BTree has its own copy of Key as well as ROWID ie, in the perfect scenario, each node is of size 32 + 8 = 40 bytes. Lets sum up sizes: 48 + 40 = 88 bytes. This is approximate cost of each record. But if, somehow, we could get rid of the table part, and store Value instead of ROWID in the index, the size will go down to 40 bytes. I am not concerned very much about actual size, but about the fact, that when I insert single record into the table, SQLite will update one or more index (BTree) pages + one or more table pages. If we could skip table updates we would save both space and I/O operations. So, having this structure, we could still perform any query imaginable, but only one specific use case will be efficient: SELECT * FROM index WHERE Key (= | > | <) ?; To sum up my gibberish: Would be nice to have ability to store both key and payload in the index. (Let's call it index-only table) This could be a feature that sets some limitations on a table, like being unable to have more than one index or inefficient table scans, but it will also give some advantage in special cases like mine. ------------------------------------- About your case, Edward. I believe you also need mapping where Key is Big (text) but Value is small (offset in a file?)? > Hi Paul: > > > Not sure about your specific use case. If you intent to query formatted text > log files, and do not need pin-point seek to a record (file scan is > acceptable), I am working on an external module that is in alpha stage. > > https://github.com/elau1004/TFR4SQLite/wiki > > > It is implemented using virtual tables. The idea of indexing these files did > cross my mind and you did beat me to asking the same question. > > > Regards. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
