在SQLite中使用索引优化查询速度(转)

create table t1
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
create table t2
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
create table t3
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);

create index idxT1 on t1(num,word1,word2);
create index idxT2Num on t2(num);
create index idxT2Word1 on t2(word1);
create index idxT2Word2 on t2(word2);
create index idxT3Word1 on t2(word1);

1) select count(*) from t1,t3 where t1.word2&＃61;t3.word2;
很慢&＃xff08;t3.word2上没有索引&＃xff09;
2) select count(*) from t3,t1 where t1.word2&＃61;t3.word2;
很慢&＃xff08;t1.word2上没有独立索引&＃xff09;
3) select count(*) from t1,t2 where t1.word2&＃61;t2.word2;
很快&＃xff08;t2.word2上有索引&＃xff09;
4) select count(*) from t2,t1 where t1.word2&＃61;t2.word2;
很慢&＃xff08;t1.word2上没有独立索引&＃xff09;
5) select count(*) from t1,t2 where t1.num&＃61;t2.num;
很快&＃xff08;t2.num上有索引&＃xff09;
6) select count(*) from t2,t1 where t1.num&＃61;t2.num;
很快&＃xff08;t1的复合索引中&＃xff0c;第一个列是num&＃xff09;
7) select count(*) from t1,t3 where t1.num&＃61;t3.num;
很慢&＃xff08;t3.num上没有索引&＃xff09;
8) select count(*) from t3,t1 where t1.num&＃61;t3.num;
很快&＃xff08;t1的复合索引中&＃xff0c;第一个列是num&＃xff09;