Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Nov 7, 2012, 06:58 PM   #1
Starfox
macrumors regular
 
Join Date: Apr 2011
SQLite speed woes - index not being used

I have an SQLite query that seems to be skipping an index I created on the field Time:

sqlite> explain query plan SELECT UserID, JSON FROM TweetsJSON WHERE TweetID <= 9999999 AND Time >= 1352249596 AND Time <= 1353027196 ORDER BY TweetID DESC LIMIT 100 ;
0|0|0|SEARCH TABLE TweetsJSON USING INTEGER PRIMARY KEY (rowid<?) (~27777 rows)

TweetID is a primary key and an index was created on Time using the syntax CREATE INDEX TimeTweetsJSONIndex ON TweetsJSON (Time). What's wrong here? Why is it skipping that index totally? Search times are reflecting that and they're less than satisfactory.
Starfox is offline   0 Reply With Quote
Old Nov 8, 2012, 07:45 AM   #2
denniscote
macrumors newbie
 
Join Date: Jul 2011
SQLite query optimizer

See section 6 of the SQLite query optimizer docs at http://www.sqlite.org/optoverview.html

It explains that only one index can be used per table, and how the optimizer selects which index to use when there are multiple candidates.

To force the optimizer to use your time index instead of the implicit tweetid index in the table you can add a unary + in front of the tweetid in your where clause. This will prevent the optimizer from using an index for the tweetid and it should then select your time index.

HTH
denniscote is offline   0 Reply With Quote
Old Nov 8, 2012, 10:52 PM   #3
Starfox
Thread Starter
macrumors regular
 
Join Date: Apr 2011
Thanks a million, Dennis! I think I know how to work around that one.
Starfox is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 03:41 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC