Optimize Query speed using indexes in SQLite

When performing a joint query on multiple tables, using indexes can significantly improve the speed. Just now, we used SQLite for a test.

Create three tables:

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 several indexes:
Table T1: composite indexes on num, word1, and word2
T2 table: There is an index on num, word1, and word2.
T3 table: There is an index on word1

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 );

Insert 10000 rows of data into each of the three tables. The num is a random number, the word1 and word2 are English words, and the corresponding num in the three tables, the word1 and word2 columns contain some identical values, but they appear in different order in the table.

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: In the two tables following the from clause, if the columns to be queried in the 2nd tables contain indexes, the query speed will be faster, and vice versa. For example, in the third query, the first table after "from" is T2, and T2 has an index on word2, so this query is fast. When you enter the SQL command and press enter, the query results are immediately displayed. However, if you use 4th query commands (that is, interchange the positions of T1 and T2), it takes 1 minute to 6 seconds to query.

It can be seen that index creation is very important to improve the speed of database query.