> > "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 that shouldn't be counted at all just because one database
>page is the minimum storage entity loaded from disk.
Hi Pavel,
I believe the OP was intrigued/upset by the fact that
SELECT count(*) WHERE NOT text IS NULL;
or (equivalent)
SELECT count(*) WHERE text IS NOT NULL
does not use an index in the text column, while
SELECT count(*) WHERE text IS NULL
does use the index.
I've shown a (trivial) way to achieve the same 'not null' count using
the index.
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 <condition>" as efficiently as it handles "<condition>, provided
such enhancement can be done with only little changes.
The optimizer is smart enough to handle multiple conditions connected
by AND and OR and use index for every condition (when they are
available, of course), but it reverts to full scan for any NOT
<condition>, whatever condition is (simple or complex).
I'm certainly not in a position to dictate how should the optimizer
should evolve but, as a mere user, I feel that situation a little less
than satisfactory.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
