Search code examples
mysqlmagentofull-text-searchinnodbmyisam

MySQL fulltext search 1 character works with MyISAM only?

I have a query which should return 1 result

SELECT * FROM 
    `catalogsearch_fulltext_scope1` AS `search_index`
WHERE
    (MATCH (data_index) AGAINST ('+Product +Set +b' IN BOOLEAN MODE));

If the table engine is MyISAM, it works.
But if I change the table back to innodb, it returns nothing.
The problem is caused by searching +b

I used show variables like 'ft_min%' to check the value is 1.

As the system is Magento 2.1, I am no idea is there any problem if I change the default engine. How can I get the result if the search condition include 1 character in innodb?

Solution

  • ft_min_word_len is for MyISAM;
    innodb_ft_min_token_size is for InnoDB.

    When changing the setting, be sure to rebuild any FULLTEXT indexes. Use OPTIMIZE TABLE or ALTER TABLE.