Usually, regular and fulltext indexing as supported by MySQL are sufficient for most use cases. There are, however, situations where they are not perfectly usable:
- InnoDB tables cannot use fulltext indexes. At the time of writing there were no signs of this changing in the foreseeable future.
- There are different ways to spell the search terms
Especially in non-English speaking countries, a problem often arises that does not surface as often in American or British environments. Words in the English language consist of the letters from A to Z without diacritics. From a software development perspective this is a welcome simplification because it allows for simpler implementations.
One problem you are often faced with German, for example, is different ways to spell the same word, making it complicated to formulate suitable search terms.
Consider the German words "Dübel" (dowel) and "Mörtel" (mortar). In a merchandise management...