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
>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 stat
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 non-contiguous for
>
>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
make
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 "col = 12345". We
> 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 under
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
>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
> 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 issue
>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
> 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
> > "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 tex
> "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 th
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 it is. 8-)
It
>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
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 list
sqlite-users@sq
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 fil
"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,
18 matches
Mail list logo