Thank you! There's one thing I don't understand though: What is the purpose of ANALYZE sqlite_master; ?
Thank you! Gerlando On Thu, Jan 24, 2019 at 4:07 PM Richard Hipp <d...@sqlite.org> wrote: > On 1/24/19, Gerlando Falauto <gerlando.fala...@gmail.com> wrote: > > Hi, > > > > I read about the skip-scan optimization: > > > > https://www.sqlite.org/optoverview.html#skipscan > > > > is there a way to check whether it is being used for a given query, or > not? > > > > Explain query plan does not seem to give any insight... > > In the output from the script below, the "ANY(a)" in the EXPLAIN QUERY > PLAN output indicates that column "a" of the index is being skipped by > the skip-scan. > > CREATE TABLE t1(a TEXT, b INT, c INT, d INT); > CREATE INDEX t1abc ON t1(a,b,c); > INSERT INTO t1 VALUES('abc',123,4,5); > INSERT INTO t1 VALUES('abc',234,5,6); > INSERT INTO t1 VALUES('abc',234,6,7); > INSERT INTO t1 VALUES('abc',345,7,8); > INSERT INTO t1 VALUES('def',567,8,9); > INSERT INTO t1 VALUES('def',345,9,10); > INSERT INTO t1 VALUES('bcd',100,6,11); > /* Fake the sqlite_stat1 table so that the query planner believes > ** the table contains thousands of rows and that the first few > ** columns are not selective. */ > ANALYZE; > DELETE FROM sqlite_stat1; > INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5000 2000 10'); > ANALYZE sqlite_master; > > EXPLAIN QUERY PLAN > SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a; > > > > > > I tried both before and after running ANALYZE; / DROP TABLE sqlite_stat1; > > but it doesn't seem to make that much of a difference. > > > > Thank you! > > Gerlando > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
