Search code examples
phpmysqlsearchfull-text-searchcodeigniter-2

MySQL MATCH AGAINST 3 character expressions

When I run the following MATCH(my_text) AGAINST ('"hey"' IN BOOLEAN MODE) I get results for all rows containing hey

When I run the following MATCH(my_text) AGAINST ('"n=3"' IN BOOLEAN MODE) I get zero results even though there are 2 rows that contain n=3

The table has fulltext index

utf8_general_ci collation

ENGINE=InnoDB

innodb_ft_min_token_size = 3

ft_min_word_len = 4

mysql version = 5.6.10

I suspect it has something to do with the = but no sure how to use *()+ to behave the way it needs to be, tried a few different combinations with no success.

Solution

  • = is not considered a word character so is not indexed

    from: https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html

    Character Set Modifications

    For the built-in full-text parser, you can change the set of characters that are considered word characters in several ways, as described in the following list. After making the modification, rebuild the indexes for each table that contains any FULLTEXT indexes. Suppose that you want to treat the hyphen character ('-') as a word character. Use one of these methods:

    Modify the MySQL source: In storage/innobase/handler/ha_innodb.cc (for InnoDB), or in storage/myisam/ftdefs.h (for MyISAM), see the

    true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.

    Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish

    letters and numbers from other characters. . You can edit the contents of the array in one of the character set XML files to specify that '-' is a “letter.” Then use the given character set for your FULLTEXT indexes. For information about the array format, see Section 10.12.1, “Character Definition Arrays”.

    Add a new collation for the character set used by the indexed columns, and alter the columns to use that collation. For general

    information about adding collations, see Section 10.13, “Adding a Collation to a Character Set”. For an example specific to full-text indexing, see Section 12.9.7, “Adding a Collation for Full-Text Indexing”.