So the missing optimization is to use a covering index vs just the index, right? Are there any plans in that direction? Or maybe a way to hint it?
And, to replace this functionality, would it be best to add a column and a trigger that calculates the length on insert or update? Or are there better ways Wout. On Wed, Dec 12, 2018 at 4:54 PM Richard Hipp <[email protected]> wrote: > On 12/12/18, Wout Mertens <[email protected]> wrote: > > sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT, > > "type" TEXT, "data" JSON); > > sqlite> CREATE INDEX "type_size" on history(type, length(data)); > > sqlite> explain query plan select type from history group by type; > > QUERY PLAN > > `--SCAN TABLE history USING COVERING INDEX type_size > > sqlite> explain query plan select type, length(data) from history group > by > > type; > > QUERY PLAN > > `--SCAN TABLE history USING INDEX type_size > > > > I would expect the latter query to use COVERING index. Is there a way to > > hint this? > > > > The query planner does not currently implement that optimization. > > -- > D. Richard Hipp > [email protected] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
