Full text search in MariaDB

Full text search (FTS) allows a sophisticated approach to text search in Totara, particularly within the catalogue. Details of how to set this up using MariaDB can be found on this page.  

Language support

MariaDB uses case- and accent- insensitive collation for full text search by default, but you can specify a different collation if required. For example:

$CFG->dboptions['ftslanguage'] = 'utf8_unicode_ci';
 $CFG->dboptions['ftslanguage'] = 'utf8mb4_0900_as_ci';
 $CFG->dboptions['ftslanguage'] = 'utf8mb4_de_pb_0900_ai_ci';

MariaDB does not support Japanese and other languages with very short words without spaces in between. You can enable the following setting to get a basic experimental support of these languages:

$CFG->dboptions['fts3bworkaround'] = true;

After making any of these changes, repopulate FTS tables by running:

php admin/cli/fts_repopulate_tables.php

Minimum search term length

MariaDB's default search character limit can be changed by editing the configuration file, as shown below:

[mysqld]
 innodb_ft_min_token_size=2
 ft_min_word_len=2

Stop words

Stop words are a set of words that will be excluded from index and search query. They depend on the language being used during indexing and search, as well as dictionary presence for database installation. For example, if the user searches for 'Hotels in Wellington' then the system excludes 'in' from the search.

More details on MariaDB stop words can be found at: https://mariadb.com/kb/en/library/full-text-index-stopwords/

Morphological search

Unfortunately, MariaDB does not support the Ngram plugin that is used in MySQL to perform word stemming, so we cannot advise at this stage how to enable morphological search in MariaDB.

Unaccented search

By default, MariaDB does support diacritics, but it depends on collation. For example, when collation is utf8_general_ci, then the search query containing either word 'första' or 'forsta' will return a record containing the word 'första'.