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<x2 > 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
