Re: [sqlite] Index usefulness for GROUP BY

2017-03-04 Thread Simon Slavin
On 4 Mar 2017, at 10:16pm, Jeffrey Mattox 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 > US

Re: [sqlite] Index usefulness for GROUP BY

2017-03-04 Thread Jeffrey Mattox
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 n

Re: [sqlite] Index usefulness for GROUP BY

2017-03-03 Thread Clemens Ladisch
Jeffrey Mattox wrote: > 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 Indexes are optimizations. In a small DB, the effect is probably not noticeable, which implies that you should not bother. Where exactly

Re: [sqlite] Index usefulness for GROUP BY

2017-03-03 Thread Hick Gunter
Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jeffrey Mattox Gesendet: Freitag, 03. März 2017 11:30 An: SQLite mailing list Betreff: [sqlite] Index usefulness for GROUP BY Given this DB schema (simplified, there are other columns): CREATE

[sqlite] Index usefulness for GROUP BY

2017-03-03 Thread Jeffrey Mattox
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_week