I would expect all queries which specify the primary key components in the WHERE clause to use the Primary Key in the query plan, regardless of if ANALYZE has been run or not.
I would also think it would assume any index which covers the most where-clause components would be the most efficient if analyze had never been ran. This doesn't appear to be the case... Example data: CREATE TABLE data (c1 INT, c2 INT, c3 INT, c4 INT, c5 TEXT, PRIMARY KEY(c1, c2)); CREATE INDEX data_idx ON data (c1, c3, c4); INSERT INTO data VALUES(1, 1, 0, 0, "test"); ...1,000,000 records later... INSERT INTO data VALUES(1, 1000000, 0, 0, "test"); Then: EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50); 0|0|0|SEARCH TABLE data USING INDEX data_idx (c1=?) (~2 rows) 0|0|0|EXECUTE LIST SUBQUERY 0 If I run ANALYZE, I get the expected result afterward: EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50); 0|0|0|SEARCH TABLE data USING COVERING INDEX sqlite_autoindex_data_1 (c1=? AND c2=?) (~50 rows) 0|0|0|EXECUTE LIST SUBQUERY 0 Tested on 3.7.15, 3.7.16, 3.7.17 ... didn't go back too far to see if this issue was introduced at some point or if it has always been this way. I've modified my code to run an Analyze on startup to work around this, but it obviously takes time to run and slows down startup. Thanks. -Brad _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
