Full text search in MariaDB
  • 06 Apr 2022
  • 1 minute to read

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/

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.

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'.

© Copyright 2022 Totara Learning Solutions. All rights reserved. Some content originally obtained via GPLv3 license and continues to be available under GPLv3. All other content is the sole copyright of Totara Learning Solutions. 


Was this article helpful?

Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.