Thanks Jens and everyone. I'll try the approach of compiling statements on the fly.
Best wishes, Hamish On Sat, 29 Feb 2020 at 23:13, Jens Alfke <j...@mooseyard.com> wrote: > > > On Feb 28, 2020, at 11:49 PM, Hamish Allan <ham...@gmail.com> wrote: > > > > Again, I may be making incorrect assumptions. > > Remember the old Knuth quote about the danger of premature optimization. > What’s the size of your data set? Have you tried making a dummy database of > the same size and experimenting with queries? > > Compiling a statement is very fast in my experience. Milliseconds. Doing > it once in response to a user command probably won’t be noticeable. > > It’s true that LIKE queries tend to be slow because they can’t, in > general, be optimized. But there are two possibilities you haven’t > mentioned: > 1. If they all involve prefix matching (“x LIKE ‘prefix%’) then SQLite > will use an index on column x to limit the search to rows where x starts > with ‘prefix’. > 2. You can create an index on a LIKE query with a specific pattern, I.e. > “CREATE INDEX foo ON table (x LIKE ‘%something%’) where (x LIKE > ‘%something%’)”. I haven’t tested, but this should speed up a query using > that specific LIKE test. > > FTS does make these types of queries faster, as long as you’re looking for > whole words or word prefixes. > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
