CODEWITHSUNDEEP
mysql
chip
chip

Reputation: 609

MYSQL full text search numbers and character being ignored?

I"m trying to do a full text search, the database I'm querying has a lot of LCD screen sizes. Eg. 32". I need to do a full text search as a search phrase can be complex, we started with a LIKE comparison but it didn't cut it.

Heres what we've got. 

SELECT stock_items.name AS si_name,
       stock_makes.name AS sm_name,
       stock_items.id AS si_id
FROM stock_items
LEFT JOIN stock_makes ON stock_items.make = stock_makes.id
WHERE MATCH (stock_items.name,
             stock_makes.name) AGAINST ('+32"' IN BOOLEAN MODE)
  AND stock_items.deleted != 1

With this search we get 0 results. Although 32" Appears multiple times in the fields. We have modified the mysql config to allow us to search 3 characters or more (instead of the default four) and searches like +NEC work fine.

My guess is here that either a) full text search in mysql ignores " character or maybe the numbers.

I don't have any control over the database data unfortunately or I'd replace the double quotes with something else. Any other solutions ?

Upvotes: 3

Views: 3167

Answers (1)

vadchen
vadchen

Reputation: 1462

MySQL ignore cirtain characters when indexing, " is one of them I presume. There are few ways to change the default character settings as described here

  • Modify the MySQL source: In myisam/ftdefs.h, 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.

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

I used the second approach and that worked for us. The provided page has a detailed example in the comments at the end of the page (comment by John Navratil).

In any case you need to rebuild the index after you changed settings: REPAIR TABLE tbl_name QUICK;

Upvotes: 1

Related Questions