Scott Robison 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
So, a newbie question here... What happens if you have,
INDEX ab on a(b);
and no "where b is not null". Doesn't the INDEX takes care of all those
nulls and the same outcome would result?
thanks.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
