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.
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.
Hope this will work for you.
SELECT MATCH (field)
AGAINST ('$search_query' IN NATURAL LANGUAGE MODE) AS result
FROM table;
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;