On 5 Feb 2012, at 8:08pm, Sherief Farouk wrote: > On Feb 5, 2012, at 2:52 PM, Simon Slavin wrote: > >> Can you show us the CREATE statement for the table ? > > CREATE TABLE Tweets(TweetID INTEGER PRIMARY KEY, UserID INTEGER, Text TEXT) > CREATE TABLE Users(UserID INTEGER PRIMARY KEY, UserName TEXT)
Okay. With this statement > SELECT TweetID, UserID, Text FROM Tweets WHERE UserID IN (SELECT UserID FROM > Users) AND TweetID <= 1234 ORDER BY TweetID DESC LIMIT 100 The SQL engine is searching and sorting by two columns. First it has to use UserID column because of your sub-select. Then it's filtering on the TweedID value for the 1234 clause. Then it has to resort the results using the TweedID column. It can't use the primary index for the ORDER BY, because it's no longer looking at the entire TABLE, just the rows it has already selected. You can probably speed this up by creating an INDEX on (UserID,TweetID) or possibly (UserID,TweetID DESC). Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
