sqlite3连接-过滤排序依据性能

Question

我正在尝试对一个sqlite3数据库执行类似的查询：

select node.loc, node.weight from node
inner join filt on (node.id = filt.node_id)
inner join filt T5 on (node.id = T5.node_id)
where (filt.word = 'aaa' and T5.word = 'aasvogel')
order by node.weight desc limit 10;

在mysql上，这样的查询速度很好，速度也很快(<0.2s)；在sqlite3上，对于相同的数据，它运行了~2s。

可能的问题是什么?我可以做些什么来提高它的性能？

我用来测试sqlite3的文件可以在这里找到：https://github.com/HoverHell/sqlperftst1

尤其是表的定义：

CREATE TABLE "node" (                        
    "id" integer PRIMARY KEY,                
    "loc" varchar(255) NOT NULL,             
    "weight" real
);
CREATE INDEX "node_loc" ON "node" ("loc");
CREATE INDEX "node_weight" on "node" ("weight");

CREATE TABLE "filt" (
    "id" integer PRIMARY KEY,
    "node_id" integer NOT NULL,
    "word" varchar(120) NOT NULL
);
CREATE INDEX "filt_word" ON "filt" ("word");
CREATE INDEX "filt_node_id" ON "filt" ("node_id");

UPD:真实数据和查询的性能比较：

​

​

Answer 1

可以通过在用于查找的两个列上创建索引来改进此查询：

CREATE INDEX filt_word_node_id ON file(word, node_id);

然而，tje数据的形状是不寻常的，这使得查询优化器错误地估计了单词查找的选择性。运行ANALYZE来修复此问题。