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 > > Regards, > > Paul. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
