Pavel Vazharov wrote: > CREATE TABLE test_event > ( > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > ... > ); > CREATE INDEX IDX_test_event_1 ON test_event (deleted, major, id); > > explain query plan select * from test_event where deleted = 0 and major = 1 > and id > 5 order by id asc limit 1; > 0 0 0 SEARCH TABLE test_event USING INDEX IDX_test_event_1 (deleted=? AND > major=?) > > So I can see that the id is not used, only delete and major part of the > compound index are used. > > Is there a way the primary key to be used as a part of the compound > index?
The rowid (which here is also the primary key) is _always_ stored in an index (after the other index columns), because this is how SQLite is able to go from an index entry to the corresponding table entry. Therefore, it is never necessary to list the rowid in the index definition, because this column is alreay there and can be used for lookups like any of the other index columns. In this case, it appears that listing the rowid explicitly confuses SQLite somehow. If you drop it from the index definition, the query will work fine: > CREATE INDEX IDX_test_event_2 ON test_event(deleted, major); > explain query plan select * from test_event where deleted = 0 and major = 1 and id > 5 order by id asc limit 1; 0|0|0|SEARCH TABLE test_event USING INDEX IDX_test_event_2 (deleted=? AND major=? AND rowid>?) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
