Hi, Would it be possible to show a message when someone creates a useless index such as index 'x' below? (at least if you have a logger defined via SQLITE_CONFIG_LOG) sqlite> create table foo(a int, b int); sqlite> create index x on foo(a,b) where a=0 and b=0; sqlite> explain query plan select * from foo where a = 0 and b = 0; 0|0|0|SCAN TABLE foo
According to the documentation partial indexes should be connected with OR, if the query itself is connected with AND: https://www.sqlite.org/partialindex.html. (Although at a conceptual level sqlite could learn that X => X, but that might lead to even more surprises when you add another field in the query condition). Also it might be nice to show a message (or a warning via SQLITE_CONFIG_LOG) when you run 'explain query plan' and there are indexes (either manual or implicit), but they were rejected for "some reason". BTW this works of course: sqlite> create index x2 on foo(a,b) where a=0 or b=0; sqlite> explain query plan select * from foo where a = 0 and b = 0; 0|0|0|SEARCH TABLE foo USING COVERING INDEX x2 (a=? AND b=?) And this works too, which is what I used in my case (a=0 is quite rare, however b=0 is almost the entire table, so a=0 OR b=0 wouldn't help): sqlite> create index x3 on foo(a,b) where a=0; sqlite> explain query plan select * from foo where a = 0 and b = 0; 0|0|0|SEARCH TABLE foo USING COVERING INDEX x3 (a=? AND b=?) Best regards, --Edwin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
