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
