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?
Advertisement
Answer
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).