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. 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 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); Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
