> > 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. When an explicit row ID is created using INTEGER PRIMARY KEY AUTOINCREMENT it's very likely that the row ID will want to be used in queries. However it seems that there's no way of optimising use of this past the simple SELECT * FROM MyTable WHERE rowid = 12345. > but does confuse the optimizer. "confuse" sounds like a synonym for "bug"... > Don't do that. Any other suggestions of how to speed this up without creating our own explicit integer primary key? Hugh ---------------------------------------------------------------------- Continuing on from my original posting, I dropped all indexes and ran: CREATE INDEX idxRowID ON Signals (sUserID) This leaves off the rowID as you suggested. However, running SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID >= 1932307 and nRowID <= 1932507 took approx 400ms. The explain output is: 0 Trace 0 0 0 EXPLAIN SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID >= 1932307 and nRowID <= 1932507 00 1 String8 0 1 0 [EMAIL PROTECTED] 00 2 Integer 1932307 2 0 00 3 Integer 1932507 3 0 00 4 Goto 0 32 0 00 5 SetNumColumns Signals (0) 7 0 00 6 OpenRead Signals (0) 4 0 00 7 SetNumColumns Signals (0) 2 0 00 8 OpenRead idxRowID (1) 281494 0 keyinfo(1,BINARY) 00 9 SCopy 1 5 0 00 10 IsNull 5 29 0 00 11 Affinity 5 1 0 ab 00 12 MoveGe idxRowID (1) 29 5 1 00 13 IdxGE idxRowID (1) 29 5 1 01 14 IdxRowid 1 9 0 00 15 MoveGe Signals (0) 0 9 00 16 IdxRowid 1 9 0 00 17 Lt 2 28 9 6c 18 IdxRowid 1 9 0 00 19 Gt 3 28 9 6c 20 Column Signals (0) 0 11 00 21 Column Signals (0) 1 12 00 22 Column Signals (0) 2 13 00 23 Column idxRowID (1) 0 14 00 24 IdxRowid 1 15 0 00 25 Column Signals (0) 5 16 00 26 Column Signals (0) 6 17 00 27 ResultRow 11 7 0 00 28 Next idxRowID (1) 13 0 00 29 Close 0 0 0 00 30 Close 1 0 0 00 31 Halt 0 0 0 00 32 Transaction 0 0 0 00 33 VerifyCookie 0 22 0 00 34 TableLock 0 4 0 Signals 00 35 Goto 0 5 0 00 Next I ran: CREATE INDEX idxRowID2 ON Signals (sUserID, nRowID2) then this query which took around 1ms: SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID2 >= 1932308 and nRowID2 <= 1932508 Explain output is: 0 Trace 0 0 0 EXPLAIN SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID2 >= 1932308 and nRowID2 <= 1932508 00 1 String8 0 1 0 [EMAIL PROTECTED] 00 2 Integer 1932308 2 0 00 3 Integer 1932508 3 0 00 4 Goto 0 35 0 00 5 SetNumColumns Signals (0) 7 0 00 6 OpenRead Signals (0) 4 0 00 7 SetNumColumns Signals (0) 3 0 00 8 OpenRead idxRowID2 (1) 241712 0 keyinfo(2,BINARY,BINARY) 00 9 SCopy 1 5 0 00 10 IsNull 5 32 0 00 11 SCopy 2 6 0 00 12 IsNull 6 32 0 00 13 Affinity 5 2 0 adb 00 14 MoveGe idxRowID2 (1) 32 5 2 00 15 SCopy 3 6 0 00 16 IsNull 6 32 0 00 17 Affinity 5 2 0 adb 00 18 IdxGE idxRowID2 (1) 32 5 2 01 19 Column idxRowID2 (1) 1 9 00 20 IsNull 9 31 0 00 21 IdxRowid 1 9 0 00 22 MoveGe Signals (0) 0 9 00 23 Column Signals (0) 0 10 00 24 Column Signals (0) 1 11 00 25 Column Signals (0) 2 12 00 26 Column idxRowID2 (1) 0 13 00 27 IdxRowid 1 14 0 00 28 Column idxRowID2 (1) 1 15 00 29 Column Signals (0) 6 16 00 30 ResultRow 10 7 0 00 31 Next idxRowID2 (1) 18 0 00 32 Close 0 0 0 00 33 Close 1 0 0 00 34 Halt 0 0 0 00 35 Transaction 0 0 0 00 36 VerifyCookie 0 21 0 00 37 TableLock 0 4 0 Signals 00 38 Goto 0 5 0 00 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
