Hi Simon, thanks for the quick and thorough reply! Inlining my response... On Thu, Mar 17, 2011 at 11:27 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 18 Mar 2011, at 6:06am, Michael Parker wrote: > >> CREATE TABLE CalendarData ( >> date date NOT NULL, >> uid char(40) NOT NULL, >> json text NOT NULL, >> PRIMARY KEY (date, uid) >> ); >> >> I just want to confirm that the index for the primary key efficiently >> handles queries using WHERE or BETWEEN on only the date field >> (avoiding a full table scan), such as the following: >> >> SELECT * from CalendarData WHERE date='2011-03-17'; >> SELECT * from CalendarData WHERE date BETWEEN '2011-02-01' and '2011-05-01'; > > Yes, your primary key as declared above will be an index which will allow > those SELECTs to be executed extremely efficiently. > >> I tried using 'explain' with the preceding queries, but the output was >> lower-level than I'd like. Any pointers on how to clearly show which >> indexes are used for a query? > > You probably want EXPLAIN QUERY PLAN rather than just EXPLAIN. But you did > devise a good index.
Yaay, that's what I was looking for, thanks! > However, you might want to reconsider your table a little if you haven't > already done too much work with it. First, there's no such data types as > 'date' or 'char'. Both of those are being interpreted as TEXT and the UID > column is not limited in size: > > http://www.sqlite.org/datatype3.html Ahh, interesting. Since the date granularity is days and does't include the time, I'll be changing date and uid to use REAL and TEXT, respectively. > Secondly my guess is that your PRIMARY KEY is actually UID, and that to truly > reflect what the data represents you should declare it like that and define a > separate INDEX on the date column. Doing this will speed up cases where > you're searching for a specific UID in the table, for example, when deleting > a row, though it will make the table use a tiny bit more storage space. You > would end up with something like > > CREATE TABLE CalendarData ( > uid TEXT PRIMARY KEY NOT NULL, > date TEXT NOT NULL, > json TEXT NOT NULL > ); > CREATE INDEX CDDate ON CalendarData (date); You are correct that uid can serve as my primary key. I proposed this schema because there is no need to select a single row for the iPhone app I'm developing: In the UI, the user can can browse by month, and days in the month for which there are rows are highlighted (using the BETWEEN query). If the user selects a day, all rows for that day are displayed underneath the calendar (using the straight WHERE query). I try not to prematurely optimize, but in truth the JSON column probably won't contain very large values, and I didn't want to create an index of comparable size to the table itself on a mobile device. But maybe that's not how the UI will be down the road, hmmm. I'll go ahead and create that separate index. Thanks for your help! - Mike _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
