On 7 Mar 2015, at 5:42am, Darren Spruell <phatbuckett at gmail.com> wrote:
> It uses SQLite and the records have a > comments field for which I'd like to use a fts4 vtable to enable > search. It sounds like you're using fts4 for just one function and you may be able to do without fts entirely. Here are three questions for you to ponder, and answer here only if you like: Am I searching Flash storage (slow) or a hard disk or SSD (fast) ? How many rows in an average installation ? How many rows in an installation with an unusually high number of rows ? Have you tried "LIKE": SELECT <whatever> FROM myTable WHERE comments LIKE '%orange%' ? You can search a lot of rows using GLOB or LIKE in very little time. Would it be possible for you to write some test code which checks this with some "average" data sizes ? A search time of 3 seconds is usually so fast your users would never dream of complaining. When SQLite retrieves data from a row it reads only up to the last column it actually needs. So to speed up searching on a column, put the column early in the table definition, or make an index containing just that column. You can also do things like SELECT rowid FROM myTable WHERE comments LIKE '%orange%' then SELECT columnsINeed FROM myTable WHERE rowid in (list of rowids returned) to fetch the actual data. But this may well be overkill for your particular app which may work acceptably fast without it. Simon.
