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 <johncrens...@priacta.com> 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<x2 you don't select almost all rows in the > 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 > <sylvain.point...@gmail.com> 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<x2 >> 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
