Just like the title says, a query on my database returns almost instantly as soon as I remove the ORDER BY primary key column clause, but takes a significantly longer time with the order by. Is there anything I can do about it? Here are the two queries (with and without ORDER BY) and their SQLite EXPLAIN output.
sqlite> explain SELECT TweetID, UserID, Text FROM Tweets WHERE UserID IN (SELECT UserID FROM Users) AND TweetID <= 1234 ORDER BY TweetID DESC LIMIT 100 ; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 OpenEphemeral 2 3 0 keyinfo(1,-BINARY) 00 2 Integer 100 1 0 00 3 Integer 1234 2 0 00 4 Goto 0 47 0 00 5 OpenRead 0 7 0 3 00 6 OpenRead 3 9 0 keyinfo(1,BINARY) 00 7 If 4 10 0 00 8 Integer 1 4 0 00 9 OpenRead 4 4 0 2 00 10 Rewind 4 35 0 00 11 Rowid 4 3 0 00 12 IsNull 3 34 0 00 13 Affinity 3 1 0 d 00 14 SeekGe 3 34 3 1 00 15 IdxGE 3 34 3 1 01 16 IdxRowid 3 5 0 00 17 Seek 0 5 0 00 18 Gt 2 33 5 6c 19 IdxRowid 3 7 0 00 20 Column 3 0 8 00 21 Column 0 2 9 00 22 MakeRecord 7 3 5 00 23 IdxRowid 3 10 0 00 24 Sequence 2 11 0 00 25 Move 5 12 1 00 26 MakeRecord 10 3 6 00 27 IdxInsert 2 6 0 00 28 IfZero 1 31 0 00 29 AddImm 1 -1 0 00 30 Goto 0 33 0 00 31 Last 2 0 0 00 32 Delete 2 0 0 00 33 Next 3 15 0 00 34 Next 4 11 0 00 35 Close 0 0 0 00 36 Close 3 0 0 00 37 OpenPseudo 5 5 3 00 38 Sort 2 45 0 00 39 Column 2 2 5 00 40 Column 5 0 7 20 41 Column 5 1 8 00 42 Column 5 2 9 00 43 ResultRow 7 3 0 00 44 Next 2 39 0 00 45 Close 5 0 0 00 46 Halt 0 0 0 00 47 Transaction 0 0 0 00 48 VerifyCookie 0 89 0 00 49 TableLock 0 7 0 Tweets 00 50 TableLock 0 4 0 Users 00 51 Goto 0 5 0 00 sqlite> explain SELECT TweetID, UserID, Text FROM Tweets WHERE UserID IN (SELECT UserID FROM Users) AND TweetID <= 1234 LIMIT 100 ; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Integer 100 1 0 00 2 Integer 1234 2 0 00 3 Goto 0 28 0 00 4 OpenRead 0 7 0 3 00 5 OpenRead 2 9 0 keyinfo(1,BINARY) 00 6 If 4 9 0 00 7 Integer 1 4 0 00 8 OpenRead 3 4 0 2 00 9 Rewind 3 25 0 00 10 Rowid 3 3 0 00 11 IsNull 3 24 0 00 12 Affinity 3 1 0 d 00 13 SeekGe 2 24 3 1 00 14 IdxGE 2 24 3 1 01 15 IdxRowid 2 5 0 00 16 Seek 0 5 0 00 17 Gt 2 23 5 6c 18 IdxRowid 2 7 0 00 19 Column 2 0 8 00 20 Column 0 2 9 00 21 ResultRow 7 3 0 00 22 IfZero 1 25 -1 00 23 Next 2 14 0 00 24 Next 3 10 0 00 25 Close 0 0 0 00 26 Close 2 0 0 00 27 Halt 0 0 0 00 28 Transaction 0 0 0 00 29 VerifyCookie 0 89 0 00 30 TableLock 0 7 0 Tweets 00 31 TableLock 0 4 0 Users 00 32 Goto 0 4 0 00 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
