This PR is part 2 (of 2), a continuation of #4. (Please note that the commit from #4 is included here.)
There are a couple of things going on here:
Search results are affected due to the use of MATCH vs LIKE
Matching only supports full words or prefixes, we can match Hello with hel*, but we can't match it with *ello.
This behaves the same as the Craft built-in search
Search results are ordered similarly to those produced by the Craft built-in search
This is due to sorting on rank (produced by MATCH) and including customRank which is likely used in the built-in search
It's not a 100% match to Craft results as there is probably still some internal sort criteria that further boosts/deprioritizes some results
I managed to get the results even closer by using subqueries and sorting them separately, but whilst it in many cases produced exact results, it skewed some others (simplicity won here)
Performance in the worst case is improved (due to MATCH vs LIKE)
Example: Across 4 spaces with non-performant keywords, search speed went from 150ms to 15ms
Non-ASCII characters are now supported (better?)
I had problems characters containing diacritics (e.g. åäö) when using LIKE and going via Go.
This seemed to be due to UTF-8 encoding which resulted in zero matches. Writing the same query in sqlite3 cli worked, on the other hand.
The FTS5 MATCH drops all diacritics so ö and o are the same.
Empty search produces more relevant matches (by sorting on customRank)
This PR is part 2 (of 2), a continuation of #4. (Please note that the commit from #4 is included here.)
There are a couple of things going on here:
MATCHvsLIKEHellowithhel*, but we can't match it with*ello.rank(produced byMATCH) and includingcustomRankwhich is likely used in the built-in searchMATCHvsLIKE)åäö) when usingLIKEand going via Go.sqlite3cli worked, on the other hand.öandoare the same.Here's a before and after: