加速SQLite查询

Question

我目前正在学习SQLite (由Python调用)。

根据我之前的问题(Reorganising Data in SQLLIte)，我想在我的数据库中存储多个时间序列(训练数据)。

我定义了以下字段：

CREATE TABLE VARLIST 
(
     VarID INTEGER PRIMARY KEY AUTOINCREMENT, 
     name TEXT UNIQUE NOT NULL
)    

CREATE TABLE DATAPOINTS 
(
     DataID INTEGER PRIMARY KEY, 
     timeID INTEGER, 
     VarID INTEGER, 
     value REAL
)

CREATE TABLE TIMESTAMPS 
(
     timeID INTEGER PRIMARY KEY AUTOINCREMENT, 
     TRAININGS_ID INT, 
     TRAINING_TIME_SECONDS FLOAT
)

VARLIST有8个条目，TIMESTAMPS 1e5条目和DATAPOINTS大约5e6。

当我现在想提取给定TrainingsID和VarID的数据时，我尝试如下：

SELECT             
    (SELECT TIMESTAMPS.TRAINING_TIME_SECONDS 
     FROM TIMESTAMPS 
     WHERE t.timeID = timeID) AS TRAINING_TIME_SECONDS,
    (SELECT value 
     FROM DATAPOINTS 
     WHERE DATAPOINTS.timeID = t.timeID and DATAPOINTS.VarID = 2) as value
FROM 
    (SELECT timeID  
     FROM TIMESTAMPS 
     WHERE TRAININGS_ID = 96) as t;

命令EXPLAIN QUERY PLAN传递：

0|0|0|SCAN TABLE TIMESTAMPS
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE TIMESTAMPS USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE DATAPOINTS

这基本上管用。

但是有两个问题：

1. 小问题:如果有一个timeID，其中没有被请求的VarID is availabe, I get an line with the valueNone`的数据。 我宁愿跳过这一行。
2. 大问题:搜索速度非常慢(使用http://sqlitebrowser.org/大约5分钟)。

我怎样才能更好地提高表现呢？

是否有更好的方法来制定SELECT命令，或者我应该修改数据库结构本身？

Answer 1

好的，根据我得到的提示，我可以非常快地通过applieng索引加速搜索如下：

CREATE INDEX IF NOT EXISTS DP_Index on DATAPOINTS (VarID,timeID,DataID);
CREATE INDEX IF NOT EXISTS TS_Index on TIMESTAMPS(TRAININGS_ID,timeID);

EXPLAIN QUERY PLAN输出现在如下所示：

0|0|0|SEARCH TABLE TIMESTAMPS USING COVERING INDEX TS_Index (TRAININGS_ID=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE TIMESTAMPS USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE DATAPOINTS USING INDEX DP_Index (VarID=? AND timeID=?)

感谢您的评论。