On 4 Mar 2017, at 10:16pm, Jeffrey Mattox <j...@mac.com> wrote:
> Thank you for your replies. I've found that my best index is on datetime
> since it eliminates the most uninteresting rows. The query plan is
>
> SEARCH TABLE History USING INDEX Idx_datetime (datetime>?)
> USE TEMP B-TREE FOR GROUP BY
> USE TEMP B-TREE FOR ORDER BY
>
>> -------------
>> Now, I look at a recent set of rows...
>>
>> SELECT TOTAL(gameCount), weekday FROM History
>> WHERE datetime >= strftime('%s','now','-28 days')
>> GROUP BY weekday
>> ORDER BY 1 DESC
Create these two additional indexes on History:
(datetime, weekday)
(weekday, datetime)
Then execute the ANALYZE command.
Then find out whether this has increased or decreased the time taken for the
SELECT.
You can delete the two indexes it turns out not to be using.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
