SQLite index Optimization Method

From: http://www.cnblogs.com/analyzer/articles/1400122.html

Speed Test results:

1) Select count (*) from T1, T3 where t1.word2 = t3.word2;
Very slow (no index on t3.word2)
2) Select count (*) from T3, T1 where t1.word2 = t3.word2;
Very slow (no independent index on t1.word2)
3) Select count (*) from T1, T2 where t1.word2 = t2.word2;
Very fast (index on t2.word2)
4) Select count (*) from T2, T1 where t1.word2 = t2.word2;
Very slow (no independent index on t1.word2)
5) Select count (*) from T1, T2 where t1.num = t2.num;
Very fast (index on t2.num)
6) Select count (*) from T2, T1 where t1.num = t2.num;
Very fast (in the compound index of T1, the first column is num)
7) Select count (*) from T1, T3 where t1.num = t3.num;
Very slow (no index on t3.num)
8) Select count (*) from T3, T1 where t1.num = t3.num;
Very fast (in the compound index of T1, the first column is num)

 

Conclusion:

1. indexing can greatly speed up queries

2. When there is a cross query, the two tables from a and B depend on whether table B has an index.

3. When the index on B is not an independent index, the query speed depends on the first field of the non-independent index.