Android: SQLite FTS3 slows down when fetching next/previous rows

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 开发者_如何学Gocan improve this speed?

EDITED:

I still can't get it to work!

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):

SELECT * FROM dictionary WHERE _id = (select max(_id) from dictionary where _id < ?)

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

If you're running LIMIT 1 to 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!