Any comments on this - Richard or Dan? Hugh
> 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? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
