This appears to be the same as http://www.sqlite.org/cvstrac/tktview?tn=1893
I've attached a comment there. This is quite a big trap which I only found when loading up the database with a lot of data. What's the timescale for fixing the bug? I will have to bypass the AUTOINCREMENT functionality and create my own IDs. That creates more complications. Hugh > *Subject:* Problems using AUTOINCREMENT row IDs in indexes > *From:* "Hugh Gibson" <[EMAIL PROTECTED]> > *To:* sqlite-users@sqlite.org > *CC:* [EMAIL PROTECTED] > *Date:* Thu, 13 Nov 2008 14:56 +0000 (GMT Standard Time) > > I'm having problems getting good index choice in SQLite 3.6.4 for a > field > which is INTEGER PRIMARY KEY AUTOINCREMENT. > > I've got the following table: > > CREATE TABLE Signals (sSignalID Text DEFAULT '',sText Text DEFAULT > '',sTime Text DEFAULT '',sUserID Text DEFAULT '',nRowID INTEGER > PRIMARY > KEY AUTOINCREMENT DEFAULT '0'); > > with index: > > CREATE UNIQUE INDEX idxUserID ON Signals (sUserID, nRowID); > > In my test cases I've got around 2 million records in the table, > with > only 6 or so sUserID values. In practice there will be thousands of > distinct sUserID values. > > This query: > > SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID >= > 1932308 > and nRowID <= 1932508 > > takes 380 ms to execute. > > If I do this: > > ALTER TABLE Signals ADD COLUMN nRowID2 INTEGER > UPDATE Signals SET nRowID2 = nRowID > CREATE UNIQUE INDEX idxUserID2 ON Signals (sUserID, nRowID2); > > and run this: > > SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID2 >= > 1932308 and nRowID2 <= 1932508 > > it takes 1 ms. > > I get similar results for this sort of query - a dramatic speedup > when > using the clone field nRowID2: > > SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID < > 1000000 > ORDER BY nRowID DESC LIMIT 100 > > Looking at the query plans it appears that the nRowID queries aren't > exploiting the fact that nRowID is in the index idxUserID. That > index is > used, but there is no seek using the nRowID as well as the sUserID. > > > Why should the fact that the nRowID field is INTEGER PRIMARY KEY > AUTOINCREMENT prevent it from being used properly in query index > selection? How can I get AUTOINCREMENT behaviour as well as good > index > selection? Is this an SQLite bug? > > Hugh > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
