On May 9, 2017 9:07 PM, "jose isaias cabrera" <jic...@barrioinvi.net> wrote:
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. Yes, it might just need to walk through a larger data set. If nulls are rare, there probably wouldn't be much of a difference (not in front of a computer to check). If the column was mostly nulls, and a lot of rows, it might be a lot faster. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
