我有一个sqlite数据库,它目前只有几个表,其中最大的一个表有超过10000行。这个表有四列:id、term、definition和category。我使用了一个FTS3模块来加快搜索速度,这帮了我很大的忙。然而,现在当我尝试从表中提取"next"或"previor"行时,它所花费的时间比我开始使用FTS3之前要长。
这就是我创建虚拟表的方式:
CREATE VIRTUAL TABLE profanity USING fts3(_id integer primary key,name text,definition text,category text);
这就是我获取下一行/前一行的方式:
SELECT * FROM dictionary WHERE _id < "+id + " ORDER BY _id DESC LIMIT 1
SELECT * FROM dictionary WHERE _id > "+id + " ORDER BY _id LIMIT 1
当我在虚拟表上运行这些语句时:
- NEXT项是在~300ms内提取
- 上一个术语是在~200ms内提取
当我用普通表(在没有FTS3的情况下创建的表)时:
- NEXT项是在~3ms内提取
- 上一个术语是在~2ms内提取
为什么会有这么大的差异?有什么办法可以提高速度吗?
编辑:
我还是不能让它工作!
您创建的虚拟表旨在提供全文查询。它的目的不是在where条件下使用PK快速处理标准查询。在这种情况下,_id列上没有索引,所以SQLite可能会执行全表扫描。下一个问题是您的查询——它完全没有效率。试试这样的东西(未经测试):
SELECT * FROM dictionary WHERE _id = (select max(_id) from dictionary where _id < ?)
接下来你可以考虑的是重新设计你的应用程序。与其加载1行,也许你应该得到40行,不如将它们加载到内存中,并在其中一端的数据少于n时进行后台数据加载。长SQL操作将对用户不可见,即使它将持续3s而不是0,3s
如果首先运行LIMIT 1
,则可以完全删除order by子句。这可能会有所帮助。不过,我对FTS3并不熟悉。
您也可以直接将id变量赋值为++或--,并断言`WHERE _id="+id+"LIMIT 1",这将进行一次查找,而不是<或>。
编辑:现在我回顾一下我键入的内容,如果你这样做,你可以完全删除LIMIT 1,因为你的_id是你的pk,必须是唯一的。
嘿,看,一个原始的where子句!