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
In my case, it's also best to have no other indexes. If I add an index on the
GROUP BY column and if that column also appears in a WHERE clause, then that
index is used and (the better) Idx_datetime is ignored. When I get a larger
dataset, I'll run ANALYZE to see if the optimizer chooses Idx_datetime (which I
know would be the best index).
Jeff
> On Mar 3, 2017, at 4:29 AM, Jeffrey Mattox <j...@mac.com> wrote:
>
> Given this DB schema (simplified, there are other columns):
>
> CREATE TABLE History (
> history_ID INTEGER PRIMARY KEY,
> gameCount INTEGER,
> weekday INTEGER, /* 0=Sunday, 6=Saturday */
> hour INTEGER, /* (0..23) */
> datetime INTEGER /* unix datetime */ );
>
> CREATE INDEX Idx_weekday ON History( weekday );
>
> -------------
> 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
>
> QUERY PLANS:
> without the index:
> 0 0 0 SCAN TABLE History
> 0 0 0 USE TEMP B-TREE FOR GROUP BY <-- weekday (7 groups)
> 0 0 0 USE TEMP B-TREE FOR ORDER BY
>
> with the index:
> 0 0 0 SCAN TABLE History USING INDEX Idx_weekday
> 0 0 0 USE TEMP B-TREE FOR ORDER BY
>
> Either way, the entire table is scanned (right?). My index covers the entire
> table, but the TEMP B-TREE FOR GROUP BY contains only the rows matching the
> WHERE clause, so the TEMP B-TREE is much smaller (right?). So, is my index
> on weekday worthwhile, time-wise and space-wise? (Query speed is not a big
> issue for me, and the DB is relatively small -- there are, at most, 60 rows
> added per day. Memory is plentiful, OSX).
>
> ---
> Jeff
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
