On Fri, Jan 22, 2016 at 1:29 PM, Richard Hipp <drh at sqlite.org <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>> wrote:
> That index will not work on queries like this (obviously): > > SELECT * FROM t1 WHERE x IS NULL; > >The index is also useless for sorting: > > SELECT * FROM t1 ORDER BY x; In addition to Dominique's question on Jan 22 about why partial indexes are not useful for sorting, I have a question on the first statement. It is clear why the condition "x IS NULL" would cause the partial index to be worthless, but in my testing, the condition "x IS NOT NULL" also causes the planner to never select this index to use. It seems to me this partial index would be exactly the best index to use because of the clause which makes it a partial index. For me, this is a not an academic discussion - I have found that I have a lot of queries that either use partial indexes or could benefit from them. -Denis On Fri, Jan 22, 2016 at 7:29 AM, Richard Hipp <drh at sqlite.org> wrote: > On 1/21/16, Denis Burke <burkeden at gmail.com> wrote: > > When > > would you NOT want to add the "where [indexedColumn] IS NOT NULL"? Seems > > like it would always be helpful. > > > > CREATE TABLE t1(x); > CREATE INDEX t1x ON t1(x) WHERE x IS NOT NULL; > > That index will not work on queries like this (obviously): > > SELECT * FROM t1 WHERE x IS NULL; > > The index is also useless for sorting: > > SELECT * FROM t1 ORDER BY x; > > Rule of thumb: Only use "WHERE x IS NOT NULL" if the column being > indexed contains many, many NULL values. > > -- > D. Richard Hipp > drh at sqlite.org >
