If I create a partial index:
create table a (a0, a1)
create index idx on a (a0) where a1 is null;
Then we have several issues:
1) This should be a covering index for
select a0, a1 from a where a1 is null;
It isn't. It's a great index, but we still defer to the table to look up
the (always null) values of a1.
2) Actually, it's slightly worse than that:
select a0, null as a1 from a where a1 is null;
will select the index, because the condition a1 matches, but it seems
that it's STILL not considered a covering index, (guessing now)
presumably because it's not SUFFICIENT that the condition on the query
is a superset of the query on the index.
Including a1 in the index makes it a covering index, despite the fact
that a1 is always null.
I care more about this than most algebraic manipulations because it's a
whole extra order of I/O in a lookup, and it's turning a 1-second query
into a 51-second query, which is now ineligible for use in a web page.
Right now, it's more worthwhile to pay the space penalty for the
additional constant value at the end of the index.
3) The third issue is almost more major than the preceding two, and has
nothing to do with covering, but with partial index selection AT ALL:
If we do a select "WHERE a1 IS ?" and pass a null for the bind-value of
? it will never select the partial index, even though it's eligible.
S.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
