> > > Any comments on this - Richard or Dan? > > > > The INTEGER PRIMARY KEY is always included in every index as an > > implied extra column on the end. If you explicitly add the > > INTEGER PRIMARY KEY as a column in the index, then you have it > > in the index twice, which serves no purpose > > I tried leaving it off the index but found that the query still runs > slowly. I've put full explain outputs below for a query with index > omitting the nRowID (INTEGER PRIMARY KEY AUTOINCREMENT) field; and > for a query with index on the clone primary key nRowID2. I get the > same speed difference as before - a factor of 300-400. SQLite still > appears to be scanning all the rows where the user ID matches rather > than using the implicit nRowID in the index.
Having said that, this is with my dataset which has few unique user IDs and therefore lots of entries for each one. Hence I'm still interested in performance with lots of user IDs and just a few rows that match per user. Later... Created 1000 users with 1000 entries each, distributed as user1, value1; user2, value2; user3, value3; ... user 1000, value 1000; user 1, value 1001 ... 1 million entries in all. Created an index on the sUserID field. Also vacuumed the database as it had various indexes added and deleted. Now the query I'm really interested in is this form: SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID < 500000 ORDER BY nRowID DESC LIMIT 100 and that is running really well now. It appears to have hit the sweet spot of using the index properly to find the last matching item in the index, and then step backwards through the index to get the items. That only takes a ms or two regardless of the rowID in use. I appreciate the SQLite package and all the hard work which has gone into it. I've got messages in this mailing list going back 5 1/2 years, and over that time it's become an integral part of our product, giving better and better service over the years as it has been improved and we have learnt how to best use it. I've banged on about this problem and thanks to your assistance it seems to be resolved now. Perhaps you could update the documentation at http://www.sqlite.org/autoinc.html and http://www.sqlite.org/lang_createindex.html - I had certainly read these many times so a note about rowid's role would be very helpful and prevent further annoying questions in the mailing list ;-). I've added a note to ticket 1893. Hugh _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
