Jean-Christophe Deschamps <j...@antichoc.net> wrote:
>>> How can indexes be used with "not null" queries?
>>
>> They cannot.
>
> If one sees NOT NULL as the complement of NULL, i.e. values in the
> range {min_value, max_value} (min and max depending on the column
> expected content and type), then couldn't an index help?
> select * from table where col between min_value and max_value
If you have an extrinsic knowledge of the domain of the column values, then
yes, you can reformulate the query as
where col1 between minValue and maxValue
I don't think SQLite would be able to rewrite the query this way automatically.
> Of course if there are only few NULLs then a table scan will probably
> be faster.
In fact, unless some 90% of the rows contain NULLs, a table scan will probably
be faster. Using an index trades O(N) performance for O(M log N), where M is
the number of rows actually satisfying the condition. This is clearly an
improvement when M is much smaller than N, and a pessimization when M is close
to N. A rule of thumb is that the break-even point is somewhere around M = 0.1*N
And if 90% of the rows do contain NULL in this column, I'd consider splitting
the data into two tables - one with three columns (containing all non-NULL rows
from the original table) and the other with two columns (containing the
remaining rows).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
