Re: [sqlite] Index without backing table

> On Mon, 03 Nov 2014 11:50:17 +0200 > Paul wrote: > > > > > 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

Re: [sqlite] Index without backing table

On Mon, 03 Nov 2014 11:50:17 +0200 Paul wrote: > > > 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

Re: [sqlite] Index without backing table

3 November 2014, 13:56:36, by "Richard Hipp" : > On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch wrote: > > > Paul wrote: > > > Are additional indices, created for WITHOUT ROWID, potentially less > > > efficient and more cumbersome? > > > > For tables with a rowid, the index

Re: [sqlite] Index without backing table

On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch wrote: > Paul wrote: > > Are additional indices, created for WITHOUT ROWID, potentially less > > efficient and more cumbersome? > > For tables with a rowid, the index stores the indexed columns and the > rowid. For WITHOUT

Re: [sqlite] Index without backing table

3 November 2014, 13:48:30, by "Clemens Ladisch" : > Paul wrote: > > Are additional indices, created for WITHOUT ROWID, potentially less > > efficient and more cumbersome? > > For tables with a rowid, the index stores the indexed columns and the > rowid. For WITHOUT ROWID

Re: [sqlite] Index without backing table

Paul wrote: > Are additional indices, created for WITHOUT ROWID, potentially less > efficient and more cumbersome? For tables with a rowid, the index stores the indexed columns and the rowid. For WITHOUT ROWID tables, the index stores the indexed columns and the primary key. Regards, Clemens

Re: [sqlite] Index without backing table

> > On 3 Nov 2014, at 9:50am, Paul wrote: > > > So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY > > as a replacement for ROWID and table itself is an index? > > It would appear that the answer is "yes". I'm not going to go beyond the > official documentation

Re: [sqlite] Index without backing table

On 3 Nov 2014, at 9:50am, Paul wrote: > So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY > as a replacement for ROWID and table itself is an index? It would appear that the answer is "yes". I'm not going to go beyond the official documentation at

Re: [sqlite] Index without backing table

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

Re: [sqlite] Index without backing table

On Sat, 01 Nov 2014 11:06:51 +0200 Paul wrote: > 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

Re: [sqlite] Index without backing table

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

Re: [sqlite] Index without backing table

On Fri, Oct 31, 2014 at 8:35 AM, Alessandro Marzocchi < alessandro.marzoc...@gmail.com> wrote: > I think what he wants is a way to create an index on a virtual table. > SQLite cannot build an index on a virtual table because SQLite has no idea if and when the content of the table will change and

Re: [sqlite] Index without backing table

On Fri, Oct 31, 2014 at 1:35 PM, Alessandro Marzocchi < alessandro.marzoc...@gmail.com> wrote: > I think what he wants is a way to create an index on a virtual table. > +1 to that. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Index without backing table

31 October 2014, 14:19:56, by "Richard Hipp" : > I don't really understand what you are asking, but I suspect that > http://www.sqlite.org/withoutrowid.html is probably the answer you are > looking for. I have just realized, how stupid my question is. Actually I don't even

Re: [sqlite] Index without backing table

I think what he wants is a way to create an index on a virtual table. I faced a similar problem recently and seeing that there was no apparent way to do that I steered to a completely different way. Lets say I have a immutable binary file which contains n rows of struct containing 10 (let's name

Re: [sqlite] Index without backing table

I don't really understand what you are asking, but I suspect that http://www.sqlite.org/withoutrowid.html is probably the answer you are looking for. On Fri, Oct 31, 2014 at 8:15 AM, Hick Gunter wrote: > Can you give an example of what such a beast would look like? > > CREATE

Re: [sqlite] Index without backing table

Can you give an example of what such a beast would look like? CREATE TABLE not_exist (A, B, C, D, E);-- for illustrative purposes CREATE INDEX no_table ON not_exist (A,B,C,D,E); -- has to be covering index The only efficient order to return and/or select rows is by {A}, {A,B}, {A,B,C}, ...