On Nov 20, 2008, at 6:20 AM, Hugh Gibson wrote: > 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 but does confuse the optimizer. Don't do that. > > > 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 D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
