On Fri, 15 Feb 2019 at 16:13, Wout Mertens <[email protected]> wrote:
> sqlite> create index b on t(b) where b is not null; > sqlite> explain query plan select b from t where b is not null; > QUERY PLAN > `--SCAN TABLE t USING COVERING INDEX b > sqlite> explain query plan select b from t where (b is not null)=1; > QUERY PLAN > `--SCAN TABLE t > > So basically, match the where of the index as part of an expression. > > I'm guessing the answer is no, but I thought I'd ask anyway > Hm, interesting. The docs have something to say about this -- from https://www.sqlite.org/expridx.html section 1: The SQLite query planner will consider using an index on an expression when > the expression that is indexed appears in the WHERE clause or in the ORDER > BY clause of a query, *exactly* as it is written in the CREATE INDEX > statement. The query planner does not do algebra. In order to match WHERE > clause constraints and ORDER BY terms to indexes, SQLite requires that the > expressions be the same, except for minor syntactic differences such as > white-space changes. > "The query planner does not do algebra" seems damning, but OTOH your query _does_ have the exact index expression embedded in the WHERE clause... -Rowan _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
