SQLite 3 integer comparison performance, ability to examine indexes

Do an experiment to understand the performance impact of the SQLite3 index on integer comparisons.

Use this test sheet to examine the green column:

Id	Name	Date
Self-increment primary key	String type, randomly generated	Integer type Randomly generated, range 0 to 54354354
1	Bmzlilzx2fblufbffj29	43232244
2	Offzjpmq5pj82mhlm7yi	18243324
3	...	...

Test the following two scenarios, respectively:

1. The date column has an index
2. Date Column no index

Insert 1 million random data each, and then execute the SQL statement 10 times

 
 
1 SELECT * FROM test_tbl
2         WHERE date BETWEEN 20000000 AND 30000000
3         ORDER BY date DESC;

Results:

	Date has index	Date no Index
Time to insert 1 million data	22.35 seconds	20.77 seconds
Time to query 10 times	0.0001442 seconds	1.5538 seconds
Remove the 3rd row of SQL statements, Time to query 10 times	0.0001146 seconds	0.0001451 seconds

It is visible that the index has a significant impact on the performance of the ORDER by date DESC clause.

SQLite 3 integer comparison performance, ability to examine indexes