On Thu, Aug 27, 2015 at 10:50 AM, Martin Kucej < i.librarian.software at gmail.com> wrote:
> Recently, I was asked to implement full-text search in an application > with up to 1 million items, each with several columns having AND, OR > and a phrase search capabilities. I can only work with FTS4, which > does not allow a phrase search with column specifiers, e. g. this is > invalid: > > MATCH 'column1:"lorem ipsum" column2:"gaudeamus igitur"' > > My question is - can I simulate phrases with the NEAR keyword? Like so: > > MATCH 'column1:lorem NEAR/0 column1:ipsum column2:gaudeamus NEAR/0 > column2:igitur' > > The way NEAR keyword is explained, it should work, and all my tests > give me correct results. I just want to make sure it is always going > to be correct. > NEAR/0 will probably not care about ordering. It might be easier to just put the columns in separate fts4 tables and join on rowid. Or phrase-match the columns separately in sub-queries against the same table (one sub-query does column1 MATCH "lorem ipsum" the other does the other match, and you join them together). I think performance-wise, joining on rowid shouldn't be a problem unless you have a very large number of hits for both queries. -scott
