Hi guys,
I've the following scenario (I'm presenting a simplified version of our scenario, but the main problem remains the same as in this scenario): CREATE TABLE test_event ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "type" INTEGER NOT NULL, "some_integer_data" INTEGER NOT NULL, "some_string_data" TEXT, "deleted" INTEGER NOT NULL, major INTEGER NOT NULL DEFAULT 0 ); CREATE INDEX IDX_test_event_1 ON test_event (deleted, major, id); When I execute: explain query plan select * from test_event where deleted = 0 and major = 1 and id > 5 order by id asc limit 1; It returns: ?selectid order from detail 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. In our scenario this leads to slow selects, really slow. I tested adding another column "dup_id" INTEGER NOT NULL, changing the index to be on (deleted, major, dup_id) and making all dup_ids equals to the corresponding ids. In this case the explain query plan explain query plan select * from test_event where deleted = 0 and major = 1 and dup_id > 5 order by dup_id asc limit 1; returns: ?selectid order from detail 0 0 0 SEARCH TABLE test_event USING INDEX IDX_test_event_1 (deleted=? AND major=? AND dup_id>?) And the select statement runs about 100 times faster than the previous one. My question is: Is there a way the primary key to be used as a part of the compound index? Adding additional column works, but it will increase the database size and will slow down the insert statements because this additional column needs to be updated with trigger or with additional query. Thanks in advance, Pavel. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
