Wildcarding FULLTEXT search in NATURAL LANGUAGE MODE

user_13521202 0
Viewed 441

I'm working with jquery autocomplete and I want to return suggestions even on partial word input.

Right now I have this query

SELECT field FROM table WHERE MATCH (field) AGAINST ('$search_query*' IN BOOLEAN MODE)");

It works well, but unlike the NATURAL LANGUAGE MODE, BOOLEAN MODE has a bad relevancy sorting.

So I'm trying to get wildcard to work in the natural language mode. As far as I know, it is not supported by default, but maybe it can be done some other way? Like using PHP function, for example.

mysql php
4 Answers

I'm almost embarrassed to admit this, but I was faced with a similar problem with non-indexed full text searching. I created a database table with my favorite word list, 18 of which look like this:

+------+-------------+
| ID   | WORD        |
+------+-------------+
| 2336 | Babble      |
| 2337 | Babbled     |
| 2338 | Babbling    |
| 2339 | Babian      |
| 2348 | Babied      |
| 2346 | Babies      |
| 2340 | Babion      |
| 2341 | Babiroussa  |
| 2342 | Babirussa   |
| 2343 | Baboo       |
| 2344 | Babu        |
| 2345 | Baby        |
| 2347 | Baby        |
| 2349 | Babying     |
| 2350 | Babylonic   |
| 2351 | Babylonical |
| 2352 | Babyroussa  |
| 2353 | Babyrussa   |
+------+-------------+

It's not very pretty, but you select the words which start with what the user types (WORD like 'bab%', as shown above for my list), excluding stop words, and then pass them into your natural language search. Word lists are available in many languages, if your application isn't English. You can add in lists of names, etc, if you like.

user_48078990

The wildcard won't work for Natural Language mode as it's an instruction used in Boolean mode.

You have to use HAVING and ORDER BY clauses, as the MATCH function returns the relevance score that you would use for sorting results (order by) and keeping only non-zero results (having).

SELECT field,
    MATCH (field) AGAINST ('$search_query*' IN BOOLEAN MODE) as score
    FROM table
    HAVING score > 0
    ORDER BY score DESC;
user_45401410

You will need something resembling

SELECT *, MATCH(field) AGAINST '$search_query' AS `relevance`
WHERE MATCH (field) AGAINST ('$search_query*' IN BOOLEAN MODE)
ORDER BY `relevance` DESC
user_475290

Hope this will work for you.

SELECT  MATCH (field)
    AGAINST ('$search_query' IN NATURAL LANGUAGE MODE) AS result
    FROM table;
user_48479440
Related