SQLite - 选择表达式很慢 [英] SQLite - select expression is very slow

问题描述

我在 SQLite 中进行查询时遇到了一些严重的性能问题.目前，activity_tbl 表中大约有 20000 个条目，activity_data_tbl 表中大约有 40 个条目.我有下面查询中使用的两列的索引，但它似乎对性能没有任何影响.

I'm experiencing some heavy performance-issues with a query in SQLite. Currently there are around 20000 entries in the table activity_tbl and about 40 in the table activity_data_tbl. I have an index for both of the columns used in the query below, but it doesn't seem to have any effect on the performance at all.

SELECT a._id, a.start_time + b.length AS time 
FROM activity_tbl a INNER JOIN activity_data_tbl b 
       ON a.activity_data_id = b._data_id 
WHERE time > ? 
ORDER BY 2 
LIMIT 1

如您所见，我选择了一个列和一个通过将两列相加而创建的值.我想这就是导致性能低下的原因，因为如果我只选择 a.start_time 或 b.length，查询会非常快.

As you can see, I select one column and a value created from adding two columns together. I guess this is what's causing the low performance, since the query is very fast if I just select a.start_time or b.length.

你们对我如何优化这个有什么建议吗?

Do you guys have any suggestion for how I could optimize this?

推荐答案

复合索引可能会有所帮助.根据其文档，如果索引有足够的信息，SQLite 会尝试避免访问该表.因此，如果引擎完成了它的功课，它将认识到索引足以计算 where 子句值并节省一些时间.如果它不起作用，则只有预计算可以.

A compound index may help. According to its docs, SQLite tries to avoid to access the table, if the index has enough information. So if the engine did its homework it will recognize that the index is enough to compute the where clause value and spare some time. If it does not work, only the pre-computation will do.

如果您经常遇到类似的任务，请阅读:http://www.sqlite.org/rtree.html

If you are more often confronted with similar tasks, please read this: http://www.sqlite.org/rtree.html

这篇关于SQLite - 选择表达式很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！