According to the Documentation https://mariadb.com/kb/en/library/fulltext-index-overview/ excluded results are partial words. Does it mean when we search for 'rown' in the phrase 'My cat is brown' with the following SQL Query
SELECT mytable.mycolumn FROM mytable
WHERE MATCH (mytable.mycolumn) AGAINST('rown*' IN BOOLEAN MODE);
the result is 0 rows?
Is this correct or I am missing something? WE are using MariaDB version 10.1.26 and InnoDB Engine
No.
There are several limitations to FULLTEXT indexing. 'rown' searches for 'rown', 'rowns', 'rowning' and a few other English variations. But it will not find brown.
The only wildcard allowed is to stick it on the end. For example, 'var*' will find 'variations'.
A + in front of a word makes it 'required'. So, AGAINST("+red +cow") will find rows with both words (scattered anywhere in the text), but not rows with only one of the words.
So, you ask "why". The answer is (1) performance and (2) utility. FULLTEXT would slow down a lot if wildcards could be at the beginning. The main use for FULLTEXT is to look for "words", not parts of words. So, you ask why allow trailing suffix. Because it is very cheap to implement. That is, the cost was low enough for the low utility.
Etc.
