Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely

Thanks for the explanation and the quick fix! Best, Manuel On Sat, May 4, 2019 at 7:41 PM Richard Hipp wrote: > Here is another case: > > CREATE TABLE t1(a,b,c); > INSERT INTO t1 VALUES(NULL,8,'yes'); > CREATE INDEX t1b ON t1(b) WHERE a IS NOT NULL; > SELECT c FROM t1 WHERE b=8 AND (a OR 1); >

Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely

Here is another case: CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(NULL,8,'yes'); CREATE INDEX t1b ON t1(b) WHERE a IS NOT NULL; SELECT c FROM t1 WHERE b=8 AND (a OR 1); The problem was in the theorem prover that determines when a partial index can be used. The problem goes all the way back to

Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely

This similar test case, that I just found now, demonstrates that this could be a pattern that is used in practice (TRUE can also be computed): CREATE TABLE t0 (c0); CREATE INDEX index_0 ON t0(c0) WHERE c0 NOTNULL; INSERT INTO t0(c0) VALUES (NULL); SELECT * FROM t0 WHERE (c0 OR TRUE); Also here,

Re: [sqlite] Partial Index and Query Planner

On 8/5/16, Detlef Golze wrote: > As documented, the query planner only uses the index if the SELECT contains > that exact same condition. > > According to .eqp with 3.12.1 the following statement indeed uses the index: > > SELECT Value1 FROM MyTable WHERE Value1<>0 AND

Re: [sqlite] Partial index to find maximum

Hick Gunter wrote: >create the primary key index ordered properly > >CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); DESC is not necessary here; SQLite has no problem reading the index in reverse order, if needed. (DESC in an index is useful only when you want to optimize multiple ORDER

Re: [sqlite] Partial index to find maximum

create the primary key index ordered properly CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); SELECT b FROM t WHERE a = ? LIMIT 1; If you insist on using a partial index for this (for example if each a has a lot of b entries) you could add a field b_is_max and keep it current using

Re: [sqlite] Partial index to find maximum

You should include both a and b in the index to be most helpful. CREATE INDEX whatever ON t (a, b); However, you say that (a, b) is already the primary key and therefore this index already exists and you do not need to create another one. Although the index will contain all rows, finding the

Re: [sqlite] Partial index to find maximum

On 29/12/2014 4:33 PM, Baruch Burstein wrote: Hi, I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a given value of 'a', the highest matching 'b'. The query itself it simple: SELECT max(b) FROM t WHERE a=:whatever To speed this up, I would add an index on 'a'. Now,

Re: [sqlite] partial index?

OK. Thanks for the clarification, Igor and Filip. I was misunderstanding the partial index to work, in effect, like a standard index on a virtual column based on a function that returns null for the "irrelevant" values, with the index defined to ignore nulls. I see now from the "inclusion" test

Re: [sqlite] partial index?

> Maybe it'll be clearer if I describe my (quite simple) use case. Our > app is caching what are basically csv files. Hundreds of files, about 2m > records per file. Sometimes we want to delete all the cache rows for one > of the files. We know ahead of time which file it will be -- let's say

Re: [sqlite] partial index?

On 19 Aug 2010, at 11:18pm, Eric Smith wrote: > The schema is roughly > > create table records(__recno INTEGER PRIMARY KEY, fileId, data); > > So sometimes we would *like* to say "delete from records where > fileId=7". > > But that is bad because does a full table scan. > > So the next cut

Re: [sqlite] partial index?

Simon Slavin wrote: > http://www.sqlite.org/lang_createview.html > > This is the SQL standard way to reduce your view of a table to just > certain rows. If I understand your request, this feature should provide > exactly what you want. Appropriate indexes will be used when consulting >

Re: [sqlite] partial index?

Kees Nuyt wrote: > You could add a "deleted" column with value range (0,1) and > create an index on it if benchmarks show that makes it > faster. As a bonus it is easier to code and maintain than a > separate table with references and triggers. > > Alternatively, you can create an composite

Re: [sqlite] partial index?

Sorry, let me amend that: > The schema is roughly > > create table records(__recno INTEGER PRIMARY KEY, fileId, data); Forget the INTEGER PRIMARY KEY. My partial index would reference the _rowid_. I don't permit vacuums on the database so, if I'm not mistaken, this shouldn't be an issue.

Re: [sqlite] partial index?

Stephen Oberholtzer wrote: > I believe what he's getting at is this: {snip explanation} You exactly understand what I'm going for and my use case. Is there a better way to implement it in sql itself than what I outlined? I.e. create my own index table that points to the proper rows and keep it

Re: [sqlite] partial index?

Igor Tandetnik wrote: > > How would you find a row whose column X contained value Y if the > > "partial" index on column X specified that rows containing value Y > > in column X should never be returned? > > No one suggests partial index should be capable of hiding anything. The > idea is

Re: [sqlite] partial index?

On Fri, Aug 20, 2010 at 06:52:40AM -0700, Cory Nelson scratched on the wall: > On Thu, Aug 19, 2010 at 1:30 PM, Eric Smith wrote: > > Afaict sqlite doesn't support indices on subsets of rows in a table, ?? > > la http://en.wikipedia.org/wiki/Partial_index -- right? > > > > Any

Re: [sqlite] partial index?

On 8/20/10, Cory Nelson wrote: > On Fri, Aug 20, 2010 at 7:39 AM, Jim Wilcoxson wrote: >> ... >> The best I could come up with is a separate table. The problem is, >> indexing the SHA1 normally means there is a copy in the row and a copy >> in the index.

Re: [sqlite] partial index?

On Fri, Aug 20, 2010 at 7:39 AM, Jim Wilcoxson wrote: > ... > The best I could come up with is a separate table. The problem is, > indexing the SHA1 normally means there is a copy in the row and a copy > in the index. Using a separate table, which still has to be indexed, >

Re: [sqlite] partial index?

On 8/20/10, Cory Nelson wrote: > +1 for this feature request. They've got a very specific and fairly > rare use case, but when opportunity strikes partial indexes are much > more convenient, straightforward, and efficient than the alternative. > > - If a table has

Re: [sqlite] partial index?

On Thu, Aug 19, 2010 at 1:30 PM, Eric Smith wrote: > Afaict sqlite doesn't support indices on subsets of rows in a table, Ю > la http://en.wikipedia.org/wiki/Partial_index -- right? > > Any plans to implement that? +1 for this feature request. They've got a very specific and

Re: [sqlite] partial index?

Tim Romano wrote: > Igor, > Here's the example where a partial index can "hide" rows. > > From the wikipedia article cited by the OP: > > > It is not necessary that the condition be the same as the index criterion; > Stonebraker's paper below presents a number of

Re: [sqlite] partial index?

On Fri, Aug 20, 2010 at 1:47 PM, Tim Romano wrote: > Igor, > Here's the example where a partial index can "hide" rows. > > From the wikipedia article cited by the OP: > > > It is not necessary that the condition be the same as the index criterion; > Stonebraker's paper

Re: [sqlite] partial index?

Igor, Here's the example where a partial index can "hide" rows. >From the wikipedia article cited by the OP: It is not necessary that the condition be the same as the index criterion; Stonebraker's paper below presents a number of examples with indexes similar to the following: create index

Re: [sqlite] partial index?

> Tim Romano wrote: >> How would you find a row whose column X contained value Y if the >> "partial" >> index on column X specified that rows containing value Y in column X >> should >> never be returned? > > No one suggests partial index should be capable of hiding

Re: [sqlite] partial index?

Tim Romano wrote: > How would you find a row whose column X contained value Y if the "partial" > index on column X specified that rows containing value Y in column X should > never be returned? No one suggests partial index should be capable of hiding anything. The idea

Re: [sqlite] partial index?

Typo: "... more performant than partial query" should read "more performant than a partial index". Tim Romano > >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partial index?

Eric, How would you find a row whose column X contained value Y if the "partial" index on column X specified that rows containing value Y in column X should never be returned? If the index hides the row, how do you cause the row to become visible to a query? You have to drop the index. However,

Re: [sqlite] partial index?

On Thu, Aug 19, 2010 at 5:53 PM, Kees Nuyt wrote: > On Thu, 19 Aug 2010 17:39:14 -0400, Eric Smith > wrote: > >>Am I missing something? > > You could add a "deleted" column with value range (0,1) and > create an index on it if benchmarks show that makes it >

Re: [sqlite] partial index?

On 19 Aug 2010, at 10:39pm, Eric Smith wrote: > I want an index that only can be used to find rows with a particular > value or set of values. Take a look at VIEWs: http://www.sqlite.org/lang_createview.html This is the SQL standard way to reduce your view of a table to just certain rows.

Re: [sqlite] partial index?

On Thu, 19 Aug 2010 17:39:14 -0400, Eric Smith wrote: >Am I missing something? You could add a "deleted" column with value range (0,1) and create an index on it if benchmarks show that makes it faster. As a bonus it is easier to code and maintain than a separate table with

Re: [sqlite] partial index?

Tim Romano wrote: > The partial index is one very messy thing, fraught with ambiguities, > something to avoid. I want an index that only can be used to find rows with a particular value or set of values. In what way is that ambiguous? Other databases (e.g. postgres) seem to support this

Re: [sqlite] partial index?

On Thu, 19 Aug 2010 17:15:40 -0400, Tim Romano wrote: >Ah, an opportunity for another purist tirade presents itself. > >I don't have a hack for SQLite but something I consider to be a much better >practice that accomplishes the same goal. If your business rules would

Re: [sqlite] partial index?

Ah, an opportunity for another purist tirade presents itself. I don't have a hack for SQLite but something I consider to be a much better practice that accomplishes the same goal. If your business rules would declare that rows with value X in column Y no longer belong to the set, the most