On Sun, Jul 18, 2010 at 12:08 AM, Simon Slavin <[email protected]> wrote:
>
> On 18 Jul 2010, at 5:46am, Peng Yu wrote:
>
>> This question may be trivial. But I'm wondering, whether I should
>> create one index on multiple columns or create multiple indexes, each
>> of which is on a single column.
>>
>> The condition of the 'select' statement can be a logic operation on
>> any of the column of the table and their combinations. I think that it
>> may be better to create an index on each column. But I'm not so sure.
>
> We would have to see an example SELECT to be sure. But even if you create
> one index on multiple columns, the order of the columns in the index matters.
> If you had three columns you could create six indexes to get each possible
> combination. Probably easier to explain to us your SELECT statements.
So, in general, what the index I should use depends on what the select
statement I might use?
Currently, I need to select all the rows that have unique content in a
subset of the columns and get such columns.
create table test (id integer primary key, value1 text, value2 text,
value3 text);
insert into test (value1,value2,value3) values('x', 'x', 'x');
insert into test (value1,value2,value3) values('x', 'x', 'y');
insert into test (value1,value2,value3) values('x', 'y', 'x');
insert into test (value1,value2,value3) values('x', 'y', 'y');
insert into test (value1,value2,value3) values('y', 'x', 'x');
insert into test (value1,value2,value3) values('y', 'x', 'y');
insert into test (value1,value2,value3) values('y', 'y', 'x');
insert into test (value1,value2,value3) values('y', 'y', 'y');
select * from test group by value1,value2;
select * from test group by value1||value2;
For this case, what is the best way to create the index?
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
