On Tue, 25 Mar 2014 20:19:40 +0000 Pavel Vazharov <pa...@anchorworks.com> wrote:
> 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); You have defined: "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT. but, a) as it's a primary key it cannot, never, be null, so "NOT NULL" has no meaning. b) you added "AUTOINCREMENT", but, in Sqlite, if you insert a row whose primary key is a integer, as NULL, it's automatically autoincremented. So Autoincrement has no meaning neither. > 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. Yes, you have a "LIMIT 1", so not need to use the last index column because no need an additional filter on id. > > 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. In indexs, the primary key is always added as last column. Sqlite uses it for retrieve the selected rows data from full table. In your first example, id is added twice to the index. > Thanks in advance, > Pavel. L --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
