> 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
