Re: [sqlite] Partial Indexes and use of LIKE

On Tue Nov 01, 2016 at 10:19:24PM -0400, Richard Hipp wrote: > On 11/1/16, Mark Lawrence wrote: > > Hello all, > > > > The documentation for partial indexes (section 3.2) lists "=, <, >, <=, > > >=, <>, or IN" as operators that will trigger the use of an > > index WHERE c IS NOT NULL. > > > > It

Re: [sqlite] Partial Indexes and use of LIKE

On 11/1/16, Mark Lawrence wrote: > Hello all, > > The documentation for partial indexes (section 3.2) lists "=, <, >, <=, > >=, <>, or IN" as operators that will trigger the use of an > index WHERE c IS NOT NULL. > > It seems to me that LIKE should also be in that list, given that it > will also

Re: [sqlite] Partial Indexes and use of LIKE

Mark Lawrence wrote: > The documentation for partial indexes (section 3.2) lists "=, <, >, <=, > >=, <>, or IN" as operators that will trigger the use of an > index WHERE c IS NOT NULL. > > It seems to me that LIKE should also be in that list, given that it > will also only match when c is not nul

[sqlite] Partial Indexes and use of LIKE

Hello all, The documentation for partial indexes (section 3.2) lists "=, <, >, <=, >=, <>, or IN" as operators that will trigger the use of an index WHERE c IS NOT NULL. It seems to me that LIKE should also be in that list, given that it will also only match when c is not null. I can force the

Re: [sqlite] Partial indexes on JSON properties?

On 10/4/16, Keith Medcalf wrote: > This raises another question. Is there any way to mark a function > in-between volatile and deterministic? Currently if the deterministic flag > is not set the optimizer assumes that the function is truly volatile (it is > called every reference, even for dupl

Re: [sqlite] Partial indexes on JSON properties?

On Monday, 3 October, 2016 12:30, Richard Hipp wrote: > On 10/1/16, Jens Alfke wrote: > > the WHERE clause in a CREATE INDEX statement > > explicitly disallows function calls Is this limitation something > > that might be lifted soon > Deterministic SQL functions are now allowed in partial

Re: [sqlite] Partial indexes on JSON properties?

> On Oct 3, 2016, at 11:29 AM, Richard Hipp wrote: > > Deterministic SQL functions are now allowed in partial index WHERE > clauses, as of a few minutes ago. The current "Prerelease Snapshot" > (https://www.sqlite.org/download.html ) > supports this capabil

Re: [sqlite] Partial indexes on JSON properties?

On 10/1/16, Jens Alfke wrote: > the WHERE clause in a CREATE INDEX statement > explicitly disallows function calls Is this limitation something that > might be lifted soon Deterministic SQL functions are now allowed in partial index WHERE clauses, as of a few minutes ago. The current "Prerele

Re: [sqlite] Partial indexes on JSON properties?

Jens Alfke wrote: > if the key is null and the table has a rowid, the node would be small, on the > order of 10 bytes or so … ? Typically less than 10 bytes. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://ma

Re: [sqlite] Partial indexes on JSON properties?

> On Oct 2, 2016, at 6:20 AM, Clemens Ladisch wrote: > > Changing the function in any way (including not registering the > function) would essentially corrupt the index. Well, the same can be said of using a custom collation function, which has been supported since 3.0; or of using a function

Re: [sqlite] Partial indexes on JSON properties?

Jens Alfke wrote: > the WHERE clause in a CREATE INDEX statement explicitly disallows > function calls, so I can’t constrain the index to only the rows that > contain the JSON property. Is this limitation something that might be > lifted soon (now that functions can be identified as ‘pure’), or is

[sqlite] Partial indexes on JSON properties?

I’m experimenting with querying databases of JSON documents. These data-sets are schemaless and there’s no guarantee that they all have a common set of properties; in fact it’s common for them to have the equivalent of multiple ‘tables’ in the same data-set, i.e. groups of documents with distinc

[sqlite] Partial indexes not working for me

Filip, I don't suppose it would fit your needs to index on the column you're comparing rather than on "id" would it? That would cause the query planner to use your indexes, I believe. create index "i1" on "t" (uniqueID) where UniqueId ==55; Alternatively, you can create an "unnecessary"

Re: [sqlite] Partial indexes not working for me

Actually running ANALYZE didn't seem to help. There are other partial indexes I tried and none of them were used: sqlite> create index "i2" on "t" ("id") where "flags" & 1; sqlite> explain query plan select * from "t" where "flags" & 1; 0|0|0|SCAN TABLE t sqlite> create index "i3" on "t" ("id") wh

Re: [sqlite] Partial indexes not working for me

On 1/29/15, Filip Navara wrote: > Hello, > > I tried really hard to get partial indexes working, but SQLite refuses to > use them: > >> create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags" INTEGER, > "uniqueId", "syncFolder" INTEGER); >> create index "i1" on "t" ("id") where "uniqueId" IS

[sqlite] Partial indexes not working for me

Hello, I tried really hard to get partial indexes working, but SQLite refuses to use them: > create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags" INTEGER, "uniqueId", "syncFolder" INTEGER); > create index "i1" on "t" ("id") where "uniqueId" IS NULL; > explain query plan select * from "t"

[sqlite] Partial indexes

Any plans to support partial indexes in SQLite? http://en.wikipedia.org/wiki/Partial_index http://db.cs.berkeley.edu/papers/ERL-M89-17.pdf PostgreSQL supports them and they seem very useful. -- View this message in context: http://www.nabble.com/Partial-indexes-tf2151623.html#a5941879 Sent f