CODEWITHSUNDEEP
phpmysqlsql
Peter
Peter

Reputation: 145

MySQL Match Against Reserved Word in Field

In a database I work with, there are a few million rows of customers. To search this database, we use a match against Boolean expression. All was well and good, until we expanded into an Asian market, and customers are popping up with the name 'In'. Our search algorithm can't find this customer by name, and I'm assuming that it's because it's an InnoDB reserved word. I don't want to convert my query to a LIKE statement because that would reduce performance by a factor of five. Is there a way to find that name in a full text search?

The query in production is very long, but the portion that's not functioning as needed is:

SELECT
    `customer`.`name`
FROM
    `customer` 
WHERE
    MATCH(`customer`.`name`) AGAINST("+IN*+KYU*+YANG*" IN BOOLEAN MODE);

Oh, and the innodb_ft_min_token_size variable is set to 1 because our customers "need" to be able to search by middle initial.

Upvotes: 2

Views: 658

Answers (1)

Wrikken
Wrikken

Reputation: 70460

It isn't a reserved word, but it is in the stopword list. You can override this with ft_stopword_file, to give your own list of stopwords. 2 possible problems with these are: (1) on altering it, you need to rebuild your fulltext index (2) it's a global variable: you can't alter it on a session / location / language-used basis, so if you really need all the words & are using a lot of different languages in one database, providing an empty one is almost the only way to go, which can hurt a bit for uses where you would like a stopword list to be used.

Upvotes: 2

Related Questions