Search code examples
mysqlmariadbmatch-against

MySQL/MariaDB match against letter 'a' and 'i' is always 0

My setup:

mysql --version
mysql  Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

and

show variables like '%ft%';
+------------------------------------------+----------------+
| Variable_name                            | Value          |
+------------------------------------------+----------------+
| aria_force_start_after_recovery_failures | 0              |
| ft_boolean_syntax                        | + -><()~*:""&| |
| ft_max_word_len                          | 84             |
| ft_min_word_len                          | 1              |
| ft_query_expansion_limit                 | 20             |
| ft_stopword_file                         | (built-in)     |
| innodb_ft_aux_table                      |                |
| innodb_ft_cache_size                     | 8000000        |
| innodb_ft_enable_diag_print              | OFF            |
| innodb_ft_enable_stopword                | ON             |
| innodb_ft_max_token_size                 | 84             |
| innodb_ft_min_token_size                 | 0              |
| innodb_ft_num_word_optimize              | 2000           |
| innodb_ft_result_cache_limit             | 2000000000     |
| innodb_ft_server_stopword_table          |                |
| innodb_ft_sort_pll_degree                | 2              |
| innodb_ft_total_cache_size               | 640000000      |
| innodb_ft_user_stopword_table            |                |
+------------------------------------------+----------------+

I'm creating a table:

CREATE TABLE `t` (
    `s` VARCHAR(32) NOT NULL,
    FULLTEXT INDEX `s` (`s`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

insert into t values ('a'), ('b'), ('c');

And then run:

select s, match(s) against('a'), match(s) against('b'), match(s) against('c') from t;
+---+-----------------------+-----------------------+-----------------------+
| s | match(s) against('a') | match(s) against('b') | match(s) against('c') |
+---+-----------------------+-----------------------+-----------------------+
| a |                     0 |                     0 |                     0 |
| b |                     0 |   0.22764469683170319 |                     0 |
| c |                     0 |                     0 |   0.22764469683170319 |
+---+-----------------------+-----------------------+-----------------------+

Why, on earth, letter 'a' is having matching score 0?

Same problem is for letter 'i', all other letters are matching as expected.

Solution

  • The result of show variables like '%ft%'; shows from your data

    innodb_ft_enable_stopword | ON

    Hence the stop word list will come into play

    http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

    For innodb we need to disable it as

    innodb_ft_enable_stopword = OFF

    After that need to restart the mysql server and finally re-built the indexes as

    repair table table_name quick