Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 01, 2010 at 11:44:51PM +0100, Jean-Christophe Deschamps scratched on the wall: > >> So indexes are not used for NOT conditions, as NOT conditions >> generally require a full scan, regardless. Yes, it is a simple >> reverse of a binary test, but the reverse of a specific indexed

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>I haven't been able to think of how it would preclude using the index, >but I suspect it's more a matter of needing a similar-but-different >codepath to optimize for the NOT case, rather than a simple "invert >this" codepath relying on the existing case. Which is really just >another way of

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 1, 2010 at 3:12 PM, Jean-Christophe Deschamps wrote: >>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT >>(col = 12345). > > You're right of course!  (and I was even saying about nulls treated apart) > > But, in your view, that the set can be

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT >(col = 12345). You're right of course! (and I was even saying about nulls treated apart) But, in your view, that the set can be non-contiguous for negative/negated conditions would it explain that current code can't

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 1, 2010 at 2:44 PM, Jean-Christophe Deschamps wrote: > The actual reason for the way NOT works as for now may be due to the > fact that negating a condition may cause the resulting set to be in > fact itself the union of two subsets. > Say the "where" condition K is

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> So indexes are not used for NOT conditions, as NOT conditions > generally require a full scan, regardless. Yes, it is a simple > reverse of a binary test, but the reverse of a specific indexed > lookup of a known value is a table scan to gather all the unknown > values. Jay, I

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 01, 2010 at 06:03:49PM +0100, Jean-Christophe Deschamps scratched on the wall: > > >I totally disagree with you. Let's say you have 1,000,000 rows and 100 > >of them contain NULL. In this situation selecting NOT NULL will select > >almost all rows which means that using index in this

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>maybe NOT is implemented the same way as any other >function and so it cannot be optimized using index. That's possible, but other logical operators don't exhibit the same bahavior and will not prevent the use of indexes. That NOT is not being handled at the same _logical_ level than AND and

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> The point was that NOT is > simply reversing the issue of a binary test, in fine, and that seems > essentially independant of the use of an index for determining . I agree with the point that NOT prevents using an index for some reason. I'm not sure but probably that's because of the same

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>I totally disagree with you. Let's say you have 1,000,000 rows and 100 >of them contain NULL. In this situation selecting NOT NULL will select >almost all rows which means that using index in this case doesn't give >any performance boost. So here using full scan for NOT NULL condition >is better

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> Anyway, it seems the OP has a point in saying that it would be nice > --and I would say 'natural'-- to have the optimizer enhanced to handle > "NOT " as efficiently as it handles ", provided > such enhancement can be done with only little changes. I totally disagree with you. Let's say you have

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> > "SELECT count(*) WHERE NOT text IS NULL" > > > > requires that the complete text column is loaded. With a stored LOB > > this results in crazy performance. > >How did you find that? What do you mean by "requires loading of the >whole text column"? It pretty much can require even loading of

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> "SELECT count(*) WHERE NOT text IS NULL" > > requires that the complete text column is loaded. With a stored LOB > this results in crazy performance. How did you find that? What do you mean by "requires loading of the whole text column"? It pretty much can require even loading of text columns

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Sun, Feb 28, 2010 at 01:26:20AM +0100, Kees Nuyt scratched on the wall: > On Sun, 28 Feb 2010 00:44:00 +0100, "Artur Reilin" > wrote: > > > "Select count(id) from table where text!='' or > > text!=NULL" is the same, right? > > text!=NULL is not a valid expression. Sure

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>It is driving me crazy. I'm working on a web spider where a table >holds the downloaded >webpage. It seems that a select > >"SELECT count(*) WHERE NOT text IS NULL" > >requires that the complete text column is loaded. With a stored LOB >this results in crazy performance. > >Is this optimized in

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Sun, 28 Feb 2010 00:44:00 +0100, "Artur Reilin" wrote: > "Select count(id) from table where text!='' or > text!=NULL" is the same, right? text!=NULL is not a valid expression. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

Hello Artur, Sunday, February 28, 2010, 12:44:00 AM, you wrote: AR> "Select count(id) from table where text!='' or text!=NULL" is the same, AR> right? Using text!='' obviously needs to load the text column. I just tried it nevertheless and aborted after 5 min (brute force reread of the whole

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

"Select count(id) from table where text!='' or text!=NULL" is the same, right? As i see you want to count all rows, right? I don't know if it is faster to only count one column instead of *, but I using it the one-column-count way. Perhaps this helps too? Artur --- Am 28.02.2010,