CODEWITHSUNDEEP
mysqlsql
CB721
CB721

Reputation: 296

Fulltext match search in natural language mode

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?

Upvotes: 0

Views: 1600

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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).

Upvotes: 1

Related Questions