Re: [sqlite] Index usefulness for GROUP BY

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

Re: [sqlite] Index usefulness for GROUP BY

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

Re: [sqlite] Index usefulness for GROUP BY

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

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 <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Index usefulness for GROUP BY Given this DB schema (simp

[sqlite] Index usefulness for GROUP BY

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

Re: [sqlite] index for a group by

On 21 Oct 2009, at 11:34pm, Sylvain Pointeau wrote: > if your "book" contains all lines (a,b,c,t,d)and you create an index > on > (a,b,c,t) I assume you meant to add ',d'in there. > then your index is as fat as your book, isn't it? Yes. And it still isn't as useful for any SELECT that

Re: [sqlite] index for a group by

On Thu, Oct 22, 2009 at 12:34:26AM +0200, Sylvain Pointeau wrote: > if your "book" contains all lines (a,b,c,t,d)and you create an index on > (a,b,c,t) > > then your index is as fat as your book, isn't it? Depends on the size of d. Also, if you add a constraint declaring t, a, b, and c (you

Re: [sqlite] index for a group by

if your "book" contains all lines (a,b,c,t,d)and you create an index on (a,b,c,t) then your index is as fat as your book, isn't it? cheers, Sylvain On Wed, Oct 21, 2009 at 11:52 PM, Simon Slavin wrote: > > On 21 Oct 2009, at 9:19pm, Sylvain Pointeau wrote: > > > Thank

Re: [sqlite] index for a group by

On 21 Oct 2009, at 9:19pm, Sylvain Pointeau wrote: > Thank you for your answers. > knowing that I have a table T (a,b,c,d,t) > where d is a value > a,b,c some dimensions > and t the time > > where I need to make a subset with a "group by" like > > select a,b,c,sum(d) > from T > where t>x1 and t

Re: [sqlite] index for a group by

ns you avoid, which makes a huge > difference. > > John > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Tuesday, October 20, 2009 2:47 PM > To: General Discussion of SQLite Database >

Re: [sqlite] index for a group by

a huge difference. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Tuesday, October 20, 2009 2:47 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] index for a group by I want to notice

Re: [sqlite] index for a group by

t; will require more btree than that, increase the size, or split across > several transactions. > > John > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Tuesday, October 20, 2009

Re: [sqlite] index for a group by

sday, October 20, 2009 7:35 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] index for a group by > please could you let me know which index could be better or faster? For this particular query index on (t,a,b,c) or even on (t,a,b,c,d) would be better and cause the query t

Re: [sqlite] index for a group by

> please could you let me know which index could be better or faster? For this particular query index on (t,a,b,c) or even on (t,a,b,c,d) would be better and cause the query to execute faster (of course if by conditions t>x1 and t also do you know by chance how to speed up the index creation?

[sqlite] index for a group by

hello, I have a table T (a,b,c,d,t) where c is a value a,b,c some dimensions and t the time I need to make a subset with a "group by" like select a,b,c,sum(d) from T where t>x1 and thttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users