SQLite使用索引前后查询时间对比

数据库约841M大小，1459万条数据，数据表如下图

 

在SQLite Expert 界面测试时间结果如下

红色为加入索引后查询时间，绿色为加入索引前查询时间，可见索引的使用极大加快了查询时间，但数据库增大50%

 

vacuum

6s/0.037s
select * from TrendTable where time="2019/11/1 15:42:25"

14.6s/0.053s
select * from TrendTable where time="2019/12/9 9:59:58"

4.8s/2.1s
select count(*) from TrendTable

12.5s/0.037s
select count(*) from TrendTable where time="2019/12/9 9:59:58"

21s/0.039s
select rowid,* from TrendTable where Time between '2019/12/26' and '2019/12/26 9:35' and Name = 'IEGT压降平均值1' and rowid not in (select rowid from TrendTable where Time between '2019/12/26' and '2019/12/26 9:35' and Name = 'IEGT压降平均值1' order by rowid ASC limit 0) order by rowid ASC limit 25;select count(*) from TrendTable where Time between '2019/12/26' and '2019/12/26 9:35' and Name = 'IEGT压降平均值1'

23s  0.8G变为1.2G
create index timeindex on TrendTable(Time)

1.5s
drop index timeindex