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 doesn't use the fields in the right order. And it's more useful for a SELECT that does use those fields in the right order. And it's less useful if you ever want to read the text in an order that makes any sense: I cannot read the index to a text book and learn the information the textbook wants to teach me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 want 't' first!) to be unique then the DB could make the whole thing smaller than if you first create the table, then the index. (I'm not sure of SQLite3 does that, but it could). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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 > group by a,b,c > > > > do you have an idea on how to choose the best index in my case? > > The first thing that this SELECT command is doing is rejecting all the > rows which do not have the right value for t. So your index should > start with t. Your SELECT is then grouping by a,b,c. So my guess at > a good index would be > > CREATE INDEX searchOnT ON T (t,a,b,c) > > You can perhaps speed up your search by replacing your 'and' with > 'between' like this: > > select a,b,c,sum(d) > from T > where t between x1 and x2 > group by a,b,c > > And for other reasons it might also be better to include d in the index: > > CREATE INDEX searchOnT ON T (t,a,b,c) > > > is it better to choose (a,b,c) ? > > This would not be as useful because the first thing the computer is > trying to do is reject most of the table first, by checking the value > of t. Only after it has done that do the values of a,b,c become > important. > > > or (a,b,c,t) ? > > It checks the value of t first, so you want to put the t first. > > > (the issue is that it is like I duplicate my table right?) > > No. An index is not like duplicating your table. Think of the TABLE > as a book, and the INDEX as the index at the back of the book. It's > not an entire copy of the book, it's a fast way of knowing which page > in the book to look at. In a normal book people do not want to find > all the green objects, all the red objects, all the blue objects, so > you do not make an index for object colours. You need to know what > people are most likely to want to find. > > I think you might find it helpful to read some basic information about > databases and indexing before you start to worry about the details of > your particular program. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 group by a,b,c > > do you have an idea on how to choose the best index in my case? The first thing that this SELECT command is doing is rejecting all the rows which do not have the right value for t. So your index should start with t. Your SELECT is then grouping by a,b,c. So my guess at a good index would be CREATE INDEX searchOnT ON T (t,a,b,c) You can perhaps speed up your search by replacing your 'and' with 'between' like this: select a,b,c,sum(d) from T where t between x1 and x2 group by a,b,c And for other reasons it might also be better to include d in the index: CREATE INDEX searchOnT ON T (t,a,b,c) > is it better to choose (a,b,c) ? This would not be as useful because the first thing the computer is trying to do is reject most of the table first, by checking the value of t. Only after it has done that do the values of a,b,c become important. > or (a,b,c,t) ? It checks the value of t first, so you want to put the t first. > (the issue is that it is like I duplicate my table right?) No. An index is not like duplicating your table. Think of the TABLE as a book, and the INDEX as the index at the back of the book. It's not an entire copy of the book, it's a fast way of knowing which page in the book to look at. In a normal book people do not want to find all the green objects, all the red objects, all the blue objects, so you do not make an index for object colours. You need to know what people are most likely to want to find. I think you might find it helpful to read some basic information about databases and indexing before you start to worry about the details of your particular program. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] index for a group by
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 twrote: > Actually, I thought exactly what you said when I saw the question. When > I saw your answer though I realized I'd been wrong, there are ways I > could slow indexing down, and therefore, ways to speed it up. > > Splitting across transactions is about the insertion of data, not the > creation of the index. This is for the case where you can't insert first > and create the index later (maybe you're inserting a lot of data into a > table that already has data for example.) The recommendation in this > case is to wrap the whole batch of inserts in a transaction, but to > commit the transaction at regular intervals, breaking the process into > multiple pieces so that you don't spill over the memory cache. > SUPPOSEDLY this positively impacts indexing performance, but I've not > personally tested that claim. The more significant impact in this case > is actually the individual transactions 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 > Subject: Re: [sqlite] index for a group by > > I want to notice, John, that my words are in context "I have table > with a lot of data, I want to create a particular index on it, how can > I do it quickly". In this context only your 5 bullet is applicable, I > admit I've forgot about that. And I don't understand how can one split > creating of index across several transactions. > > Pavel > > On Tue, Oct 20, 2009 at 2:15 PM, John Crenshaw > wrote: > >> Nothing in this process can be sped up. > > > > Actually, that isn't entirely true. While it always requires a full > data > > scan, Some things can make the indexing part of the process faster. > > Since indexing is done basically using a comparative sort, anything > that > > would speed up the sort, will speed up the indexing. > > 1. It is faster to sort 1000 data points, than to insert 1000 > datapoints > > into a constantly sorted list. Creating the index after all inserts is > > faster than creating the index, then inserting. > > 2. If possible, avoid indexes on long data strings, since the compares > > can be time consuming. > > 3. If you have a field that stores one of several strings (as an > "enum") > > consider using integers instead. Integers have lower overhead, and can > > be compared (and sorted) more quickly than strings. > > 4. If you are feeling really gutsy, you could mod the code and > implement > > a radix sort or something similar for integer values. I'm not really > > recommending this, just saying, inserts and lookups in a radix index > are > > faster than a btree. > > 5. Make sure the memory cache is large enough for the sort. Writing > data > > to disk is very costly, compared to sorting in memory. Default is 2000 > > pages (2MB) worth of btree data. If you are about to build an index > that > > 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 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 to execute faster (of course if by > > conditions t>x1 and t > table). > > > >> also do you know by chance how to speed up the index creation? > > > > There's no way to do that. SQLite have to scan the whole table, read > > data from all rows and put necessary information into the index. > > Nothing in this process can be sped up. > > > > Pavel > > > > On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau > > wrote: > >> 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 &quo
Re: [sqlite] index for a group by
Actually, I thought exactly what you said when I saw the question. When I saw your answer though I realized I'd been wrong, there are ways I could slow indexing down, and therefore, ways to speed it up. Splitting across transactions is about the insertion of data, not the creation of the index. This is for the case where you can't insert first and create the index later (maybe you're inserting a lot of data into a table that already has data for example.) The recommendation in this case is to wrap the whole batch of inserts in a transaction, but to commit the transaction at regular intervals, breaking the process into multiple pieces so that you don't spill over the memory cache. SUPPOSEDLY this positively impacts indexing performance, but I've not personally tested that claim. The more significant impact in this case is actually the individual transactions 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 Subject: Re: [sqlite] index for a group by I want to notice, John, that my words are in context "I have table with a lot of data, I want to create a particular index on it, how can I do it quickly". In this context only your 5 bullet is applicable, I admit I've forgot about that. And I don't understand how can one split creating of index across several transactions. Pavel On Tue, Oct 20, 2009 at 2:15 PM, John Crenshaw wrote: >> Nothing in this process can be sped up. > > Actually, that isn't entirely true. While it always requires a full data > scan, Some things can make the indexing part of the process faster. > Since indexing is done basically using a comparative sort, anything that > would speed up the sort, will speed up the indexing. > 1. It is faster to sort 1000 data points, than to insert 1000 datapoints > into a constantly sorted list. Creating the index after all inserts is > faster than creating the index, then inserting. > 2. If possible, avoid indexes on long data strings, since the compares > can be time consuming. > 3. If you have a field that stores one of several strings (as an "enum") > consider using integers instead. Integers have lower overhead, and can > be compared (and sorted) more quickly than strings. > 4. If you are feeling really gutsy, you could mod the code and implement > a radix sort or something similar for integer values. I'm not really > recommending this, just saying, inserts and lookups in a radix index are > faster than a btree. > 5. Make sure the memory cache is large enough for the sort. Writing data > to disk is very costly, compared to sorting in memory. Default is 2000 > pages (2MB) worth of btree data. If you are about to build an index that > 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 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 to execute faster (of course if by > conditions t>x1 and t table). > >> also do you know by chance how to speed up the index creation? > > There's no way to do that. SQLite have to scan the whole table, read > data from all rows and put necessary information into the index. > Nothing in this process can be sped up. > > Pavel > > On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau > wrote: >> 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 t> group by a,b,c >> >> I created an index on a,b,c >> but this table is large and the index creation is time consuming (few > hours) >> >> please could you let me know which index could be better or faster? >> also do you know by chance how to speed up the index creation? >> >> Best regards, >> Sylvain >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > s
Re: [sqlite] index for a group by
I want to notice, John, that my words are in context "I have table with a lot of data, I want to create a particular index on it, how can I do it quickly". In this context only your 5 bullet is applicable, I admit I've forgot about that. And I don't understand how can one split creating of index across several transactions. Pavel On Tue, Oct 20, 2009 at 2:15 PM, John Crenshaw wrote: >> Nothing in this process can be sped up. > > Actually, that isn't entirely true. While it always requires a full data > scan, Some things can make the indexing part of the process faster. > Since indexing is done basically using a comparative sort, anything that > would speed up the sort, will speed up the indexing. > 1. It is faster to sort 1000 data points, than to insert 1000 datapoints > into a constantly sorted list. Creating the index after all inserts is > faster than creating the index, then inserting. > 2. If possible, avoid indexes on long data strings, since the compares > can be time consuming. > 3. If you have a field that stores one of several strings (as an "enum") > consider using integers instead. Integers have lower overhead, and can > be compared (and sorted) more quickly than strings. > 4. If you are feeling really gutsy, you could mod the code and implement > a radix sort or something similar for integer values. I'm not really > recommending this, just saying, inserts and lookups in a radix index are > faster than a btree. > 5. Make sure the memory cache is large enough for the sort. Writing data > to disk is very costly, compared to sorting in memory. Default is 2000 > pages (2MB) worth of btree data. If you are about to build an index that > 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 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 to execute faster (of course if by > conditions t>x1 and t table). > >> also do you know by chance how to speed up the index creation? > > There's no way to do that. SQLite have to scan the whole table, read > data from all rows and put necessary information into the index. > Nothing in this process can be sped up. > > Pavel > > On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau > wrote: >> 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 t> group by a,b,c >> >> I created an index on a,b,c >> but this table is large and the index creation is time consuming (few > hours) >> >> please could you let me know which index could be better or faster? >> also do you know by chance how to speed up the index creation? >> >> Best regards, >> Sylvain >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] index for a group by
> Nothing in this process can be sped up. Actually, that isn't entirely true. While it always requires a full data scan, Some things can make the indexing part of the process faster. Since indexing is done basically using a comparative sort, anything that would speed up the sort, will speed up the indexing. 1. It is faster to sort 1000 data points, than to insert 1000 datapoints into a constantly sorted list. Creating the index after all inserts is faster than creating the index, then inserting. 2. If possible, avoid indexes on long data strings, since the compares can be time consuming. 3. If you have a field that stores one of several strings (as an "enum") consider using integers instead. Integers have lower overhead, and can be compared (and sorted) more quickly than strings. 4. If you are feeling really gutsy, you could mod the code and implement a radix sort or something similar for integer values. I'm not really recommending this, just saying, inserts and lookups in a radix index are faster than a btree. 5. Make sure the memory cache is large enough for the sort. Writing data to disk is very costly, compared to sorting in memory. Default is 2000 pages (2MB) worth of btree data. If you are about to build an index that 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 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 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? There's no way to do that. SQLite have to scan the whole table, read data from all rows and put necessary information into the index. Nothing in this process can be sped up. Pavel On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau wrote: > 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 t group by a,b,c > > I created an index on a,b,c > but this table is large and the index creation is time consuming (few hours) > > please could you let me know which index could be better or faster? > also do you know by chance how to speed up the index creation? > > Best regards, > Sylvain > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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? There's no way to do that. SQLite have to scan the whole table, read data from all rows and put necessary information into the index. Nothing in this process can be sped up. Pavel On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau wrote: > 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 t group by a,b,c > > I created an index on a,b,c > but this table is large and the index creation is time consuming (few hours) > > please could you let me know which index could be better or faster? > also do you know by chance how to speed up the index creation? > > Best regards, > Sylvain > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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