On 11/3/16, Quan Yong Zhai <[email protected]> wrote: > Dear SQLite developers, > > SQLite does not use covering index on following situation > CREATE TABLE t(x json); > CREATE INDEX i1 on t( json_extract(x, "$.name"));
I have this on my to-do list. But there are technical difficulties in the implementation that I have not yet figured out. Here is a summary of current implementation: (1) During initial analysis and name resolution, SQLite constructs a bitmap that defines which columns of each table are used. The left-most column is bit 0. The second column is bit 1. And so forth. The 63rd bit is set if any column beyond the 63rd column of the table is used. (2) During query planning, the optimizer knows that an index can be a covering index by comparing a bitmask of the columns contained in the index against the bitmap of columns actually used in the query. (Astute readers may notice, therefore, that SQLite will not use an index as a covering index if the index references any column of the table past the first 63 columns.) The problem is that an expression is not a column. And so we cannot construct bitmaps for indexes on expressions and use those bitmaps to determine coverage. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
