> 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 understand how your argument goes, but I still have difficulty buying
it. Why not negate the condition itself and then only proceed?
Under any condition K, a table T is the union of the subset X of
elements that match the condition K and the subset Y of elements that
don't. In other words, Y is the subset whose elements match [not
K]. In SQL there is of course the special case for nulls.
You're saying that selecting all rows in T that match K is OK for
indexing, while selecting rows in T which match the negated condition
K' = not K can only be done with a full scan.
That is true iff the complementation is done afterwards or on the fly
(full scan). My point is that NOT seems to be acting as a
complementation operator: select X then full scan and check every row
if it belongs to X or not.
I'd rather see the negated condition K' (= not K) as the condition used
for indexed selection, again when using the 'complementary' condition
makes sense.
Pavel modified simple example works well here:
condition " ... > 3" OK for index search
condition " ... <= 3" OK for index search
condition "NOT ... > 3" only full scan: why?
I still believe that it's possible that many simple conditions be
negated and used negated for index search.
Now, that it would easy to implement in the current SQLite code is
another matter entirely and I never pretended it could be done within
minutes.
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 can see the index
split into not less than four subsets:
o) N = {rows with col is null}
o) X = {rows with col < 12345}
o) Y = {rows with col = 12345}
o) Z = {rows with col > 12345}
For the "positive" condition K, the resulset is Y.
For the negated condition K' (col <> 12345) the resulset is N u X u Z,
made of two (or three ?) distinct blocks of indexed rowids. A possible
explanation is that the current code may be unable to cope with
situations where the resultset is not index-wise contiguous.
The OP request can be written in a number of ways to make use of the
index, even:
select count(*) from mybigtable where mytextcolumn < '' or
mytextcolumn >= '';
so the question is not if SQLite can process equivalent count/querries
efficiently or not. I nonetheless think "is not null" is much clearer
than the above workaround.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
