The assertion that AUTOINCREMENT has no effect (here and in another post) is incorrect. Without it, SQLite MAY reuse a key from deleted rows; with it, this will not happen.
Sent from Samsung Galaxy Note -------- Original message -------- From: Simon Slavin <slav...@bigfraud.org> Date: 26/03/2014 12:50 (GMT+00:00) To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Primary key used in compound index On 25 Mar 2014, at 8:19pm, Pavel Vazharov <pa...@anchorworks.com> wrote: > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Can you try this as exactly the following: "id" INTEGER PRIMARY KEY, leaving everything else out. I assure you that it will obey the 'NOT NULL' and 'AUTOINCREMENT' anyway. The reason is that SQLite has explicit understanding of exactly that phrase for primary keys. > "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. Not sure whether the id column should be mentioned but the index you created is an excellent index for that SELECT, so you should be getting extremely fast results. Can you try using EXPLAIN instead of EXPLAIN QUERY PLAN ? It will tell you more about how the query is being handled. > And the select statement runs about 100 times faster than the previous one. I agree. Makes no sense. > My question is: Is there a way the primary key to be used as a part of the > compound index? You should think of SQLite sneakily adding the primary key onto the end of every index you declare, whether you ask for it or not. When devising searching and sorting methods, SQLite knows it's there and should be using it correctly. > 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. I agree this is annoying and we should be able to solve your strange problem. Can you post the following information: A) What version of SQLite are you using ? B) What is the result of EXPLAIN SELECT ? C) How many rows are there in that table ? D) Are you testing this inside your own application or using the SQLite shell tool ? If you want to make it really easy for us to test this, could you put up some test data on a server somewhere ? But we may not need it so don't work too hard on it at this stage. Note to others: I get the same results from XQP using SQLite 3.7.13 and no data in the table. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
