0

I am attempting a fulltext search in mysql. I expect that when I pass in a string, I will receive ranked by relevancy when I use [Natural Language Mode]mysql - fulltext index - what is natural language mode .

Here is how I created the index: CREATE FULLTEXT INDEX item_name ON list_items(name);

When I use LIKE, I receive results, except I want to order them by relevancy. Hence, the fulltext search.

Here is the query I have using LIKE: SELECT name FROM list_items WHERE name LIKE "%carro%"; Which results in Carrots, Carrots, Carrots etc.

Here is the query I have attempting the MATCH search: SELECT name FROM list_items WHERE MATCH(name) AGAINST('carro' IN NATURAL LANGUAGE MODE); Which returns no results.

I am basing my query on the selected answer on this post: Order SQL by strongest LIKE? And this page: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

Even when I run the query without Natural Language Mode or even in Boolean Mode, I don't get any results. What am I missing?

CB721
  • 256
  • 4
  • 15
  • A fulltext search by default searches for complete words. To find words that *start* with a text, use a wildcard (`'carro*'`). This cannot find text in the middle of a word (as you can with `like '%carro%'`). Also be aware what relevance means and that you have little control over what MySQL considers relevant. It's calculated by how rare a word is. If your column contains "Carrot" 3 times and "Carrotcake" once, carrotcake is more relevant than carrot, even if you think that "carrot" looks more similar to "carro*". Test it, but check if a fulltext index is the right tool for your requirements. – Solarflare Apr 29 '20 at 08:34
  • So would using `LIKE` here be a better option? – CB721 Apr 29 '20 at 14:20
  • Since you didn't specify your requirements (e.g. if you want to seach in the middle of words, or what you consider to be relevant), I cannot tell you if `like` is a better option. I just wanted to comment that you should test and check (or elaborate on) your requirements if they fit what a fulltext search can (and cannot) do (and we may be able to guide you to alternative solutions if it cannot). – Solarflare Apr 29 '20 at 22:19

1 Answers1

1

You seem to want to use * as a wildcard. For that you need to use "boolean" mode rather than "natural language". So, this might do what you want:

SELECT name
FROM list_items
WHERE MATCH(name) AGAINST('carro*' IN BOOLEAN MODE)

This still produces a relevance ranking, although it might not be exactly the same as natural language mode.

Also note that this will get matches such as "carrouse".

I don't think that MySQL supports synonym lists for full text search, so this is tricky to avoid (although like filtering along with the full text filtering might suffice).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I run your answer as is, I get this error: `Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LANGUAGE MODE)' at line 1` – CB721 Apr 29 '20 at 14:31