I'm experimenting with covering indices on one of our larger tables. I started seeing really inconsistent behavior, and made the following sample setup code that demonstrates it:
DROP TABLE IF EXISTS test; CREATE TABLE test( col01 integer,col02 integer,col03 integer,col04 integer,col05 integer,col06 integer,col07 integer,col08 integer,col09 integer,col10 integer, col11 integer,col12 integer,col13 integer,col14 integer,col15 integer,col16 integer,col17 integer,col18 integer,col19 integer,col20 integer, col21 integer,col22 integer,col23 integer,col24 integer,col25 integer,col26 integer,col27 integer,col28 integer,col29 integer,col30 integer, col31 integer,col32 integer,col33 integer,col34 integer,col35 integer,col36 integer,col37 integer,col38 integer,col39 integer,col40 integer, col41 integer,col42 integer,col43 integer,col44 integer,col45 integer,col46 integer,col47 integer,col48 integer,col49 integer,col50 integer, col51 integer,col52 integer,col53 integer,col54 integer,col55 integer,col56 integer,col57 integer,col58 integer,col59 integer,col60 integer, col61 integer,col62 integer,col63 integer,col64 integer,col65 integer,col66 integer,col67 integer,col68 integer,col69 integer,col70 integer ); CREATE INDEX test1 ON test(col01,col02,col03); CREATE INDEX test2 ON test(col01,col02,col63); CREATE INDEX test3 ON test(col62,col63,col64); CREATE INDEX test4 ON test(col64,col65,col66); CREATE INDEX test5 ON test(col66,col67,col10); --With that initial setup, here's a pile of sample EXPLAIN QUERY PLANs and their result, as to whether the covering index works: EXPLAIN QUERY PLAN SELECT SUM(col03) FROM test WHERE col01=0 AND col02=1; --test1 COVERING EXPLAIN QUERY PLAN SELECT SUM(col63) FROM test WHERE col01=0 AND col02=1; --test2 COVERING EXPLAIN QUERY PLAN SELECT col02,col63 FROM test WHERE col01=0; --test2 COVERING EXPLAIN QUERY PLAN SELECT SUM(col64) FROM test WHERE col62=0 AND col63=1; --test3 NONCOVERING EXPLAIN QUERY PLAN SELECT col63 FROM test WHERE col62=0; --test3 COVERING EXPLAIN QUERY PLAN SELECT col63,col64 FROM test WHERE col62=0; --test3 NONCOVERING EXPLAIN QUERY PLAN SELECT SUM(col66) FROM test WHERE col64=0 AND col65=1; --test4 NONCOVERING EXPLAIN QUERY PLAN SELECT SUM(col10) FROM test WHERE col66=0 AND col67=1; --test5 NONCOVERING EXPLAIN QUERY PLAN SELECT col67,col10 FROM test WHERE col66=0; --test5 NONCOVERING Help? :) We'd really rather keep our table denormed, as we really do pull and use all >64 columns of data with every query, but if this is just a limitation, then we'll have to figure out a solution. It's not listed anywhere on the limits page, though, so I'm wondering if this needs to be added, or if I'm just doing something stupid or something. :) Thanks! -David _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
