On Mon, May 8, 2017 at 8:20 PM, Scott Robison <sc...@casaderobison.com> wrote:
> On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za> > wrote: > > Hi, > > > > I use a lot of indexes on fields that typically contain lots of NULLs, so > > the WHERE NOT NULL partial indexing seems very useful. > > > > However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. > a > > normal index, SQLite won't use the index to find Max(IndexedField) if it > is > > a partial index. > > > > Is this an optimization opportunity? I understand that other kinds of > > partial indexes might exclude possible Min or Max values, but a NOT NULL > > index would be fine for mins, maxes and most other things? > > This may be an optimization opportunity, but you can easily force the > use of that index by stating WHERE NOT NULL in the select query > itself. For example: > > CREATE TABLE a(b); > CREATE INDEX ab on a(b) where b is not null; > > sqlite> explain query plan select max(b) from a; > 0|0|0|SEARCH TABLE a > sqlite> explain query plan select max(b) from a where b is not null; > 0|0|0|SEARCH TABLE a USING COVERING INDEX ab > > > Thanks Scott, That works and fully solves my problem! Regards, Paul. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
