I added a FULLTEXT index to a table like so:

ALTER TABLE TEST ADD FULLTEXT(name, descrip, notes);

The TEST table has 100 rows. I updated the descrip column with 'branch' in one row and 'Branches' in another row.

Then I run

SELECT * FROM TEST WHERE MATCH(name, descrip, notes) AGAINST ('branch' IN BOOLEAN MODE);

The result returned only rows that contain 'branch'. 'Branches' is not included in the result, even though it is comprised of the word that was searched. How to include 'Branches'? I want to avoid using LIKE.

  • What is the relevance for that phrases? Check for SELECT *, MATCH(name, descrip, notes) AGAINST ('branch' IN BOOLEAN MODE) AS relevance FROM test WHERE LOCATE(CONCAT(name, descrip, notes), 'Branches');... and try the same, but IN NATURAL LANGUAGE MODE. – Akina Jun 15 '18 at 4:29
  • ...AGAINST ('branch*' IN BOOLEAN MODE);? – Michael - sqlbot Jun 15 '18 at 9:43
  • What version of MySQL? Which Engine? – Rick James Jun 18 '18 at 16:54
  • Sounds like a bug report is needed: bugs.mysql.com -- bugs.mysql.com/bug.php?id=15085 – Rick James Jun 18 '18 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.