Reply To: (Fulltext) search broken

09/01/2016 at 12:09 pm #389171
Steven Zahm
Keymaster

@ Thomas

The login/pass worked to get to the site but it still requires a login to view the directory.

The LIKE query is basic. It simply matches words that begin with the keywords that were entered in the selected fields.

The FULLTEXT is more complex but this is handled by the database, not Connections. The database matches against the keyword terms that were entered and orders the results according to a score in the selected fields.

As to why “jour” was working and “journal” was not…

The FULLTEXT is matching against the whole word, not a partial like the LIKE query. Now, when a FULLTEXT query returns zero results, it’ll fallback to a LIKE query automatically.

So with FULLTEXT enabled here’s what was happening so you can see why you were getting the results you were:

  • “jour” (found) — No FULLTEXT results, fallback to LIKE, returns partial match results.
  • “journal” (not found) — found FULLTEXT results, no fallback to LIKE, returns FULLTEXT whole word matches
  • “journali” (found) — No FULLTEXT results, fallback to LIKE, returns partial match results.
  • “journalist” (not found) — found FULLTEXT results, no fallback to LIKE, returns FULLTEXT whole word matches
  • “journalistin” (found) — found FULLTEXT results, no fallback to LIKE, returns FULLTEXT whole word matches

With LIKE only enabled, you will always receive matches to keywords that being with the entered terms.

All this said, it does sound like everything is work quite correctly.

At one point I did have the FULLTEXT configured to do keyword begins with queries but the feedback I received from quite a few others was that is returned results were too “wide” to be useful. So I made FULLTEXT query whole word only and that seems to satisfy most nearly everyone.

Now, if you really want to have partial term matching with FULLTEXT, there is a filter which you can hook into with a bit of PHP code to change FULLTEXT query back to partial term matching. (NOTE: this will be terms that start with only, FULLTEXT does not support partial match where the term ins within the word).

I hope this thoroughly answers your question.