- 06 Apr 2022
- 1 minute to read
Full text search in MariaDB
- Updated on 06 Apr 2022
- 1 minute to read
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.
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:
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 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 2023 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.