On Mon, Oct 17, 2011 at 9:14 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> 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.
>
It will if you use the latest SQLite from trunk, compiled using
-DSQLITE_ENABLE_STAT3 and if you run ANALYZE and if enough entries in the
table are NULL to justify using an index to find the ones that are NOT
NULL. As you point out below, "enough" usually means about 90%.
>
> > 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
