> 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
