Android：SQLite FTS3 在获取下一行/上一行时速度变慢
我有一个 sqlite 数据库，目前只有几个表，其中最大的一个有超过 10,000 行。该表有四列：id、term、definition、category。我使用了 FTS3 模块来加快搜索速度，这很有帮助。然而，现在当我尝试从表中获取“下一个”或“上一个”行时，它花费的时间比开始使用 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
当我在虚拟表上运行这些语句时：
- 下一个术语是在 ~300ms 内获取，
- 上一个术语是在 内获取>~200ms
当我使用普通表（没有 FTS3 创建的表）时：
- 下一个术语是在 ~3ms 内获取，
- 上一个术语是在 ~3ms 内获取~2ms
为什么会有这么大的差别呢？有什么办法可以提高这个速度吗？
编辑：
我仍然无法让它工作！
I have a sqlite db that at the moment has few tables where the biggest one has over 10,000 rows. This table has four columns: id, term, definition, category. I have used a FTS3 module to speed up searching which helped a lot. However, now when I try to fetch 'next' or 'previous' row from table it takes longer than it was before I started using FTS3.
This is how I create virtual table:
CREATE VIRTUAL TABLE profanity USING fts3(_id integer primary key,name text,definition text,category text);
This is how I fetch next/previous rows:
SELECT * FROM dictionary WHERE _id < "+id + " ORDER BY _id DESC LIMIT 1
SELECT * FROM dictionary WHERE _id > "+id + " ORDER BY _id LIMIT 1
When I run these statements on the virtual table:
- NEXT term is fetch within ~300ms,
- PREVIOUS term is fetch within ~200ms
When I do it with normal table (the one created without FTS3):
- NEXT term is fetch within ~3ms,
- PREVIOUS term is fetch within ~2ms
Why there is such a big difference? Is there any way I can improve this speed?
EDITED:
I still can't get it to work!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您创建的虚拟表旨在提供全文查询。它的目的不是在 where 条件下使用 PK 快速处理标准查询。
在这种情况下，_id 列上没有索引，因此 SQLite 可能会执行全表扫描。
下一个问题是你的查询 - 它完全没有效率。尝试这样的事情（未经测试）：
接下来您可以考虑的是重新设计您的应用程序。也许您应该加载 1 行，而不是加载 40 行，将它们加载到内存中，并在其中一端的数据少于 n 时加载后台数据。长 SQL 操作将变得对用户不可见，即使它持续 3 秒而不是 0,3 秒
Virtual table you've created is designed to provide full text queries. It's not aimed to fast processing standard queries using PK in where condition.
In this case there is no index on your _id column, so SQLite probably performs full table scan.
Next problem is your query - it's totally inefficient. Try something like this (untested):
Next thing you can consider is redesign of your app. Instead of loading 1 row you, maybe you should get let's say 40, load them into memory and make background data loading when there is less than n to one of the ends. Long SQL operation will become invisible to user even if it'll last 3s instead of 0,3s
如果您一开始就运行
LIMIT 1，则可以完全删除 order by 子句。这可能会有所帮助。不过，我对 FTS3 并不熟悉。您也可以直接将 id 变量分配为 ++ 或 -- 并断言 `WHERE _id = "+id+" LIMIT 1" 这将进行一次查找而不是 < 或 >。
编辑：现在我看回到我输入的内容，如果你这样做，你可以完全删除 LIMIT 1，因为你的 _id 是你的 pk 并且必须是唯一的，
嘿看，一个原始的 where 子句！
If you're running
LIMIT 1to begin with, you can remove the order by clause completely. This may help. I'm not familiar with FTS3, however.You could also just flat out assign your id variable a ++ or -- and assert `WHERE _id = "+id+" LIMIT 1" which would make a single lookup instead of < or >.
Edit: and now that I look back at what I typed, if you do it that way, you can just remove LIMIT 1 completely, since your _id is your pk and must be unique.
hey look, a raw where clause!