CODEWITHSUNDEEP
mysqlfull-text-search
Maxim Kuleshov
Maxim Kuleshov

Reputation: 146

How to make MySQL MATCH...AGAINST use various word separators?

I have a table with 300K string values. These values contain all types of word separators so it looks like this:

id  value
1   A B C
2   A B_C
3   A_B-C
4   A-B-C

Let's say I want to find all four rows containing A and B. This query
SELECT * FROM table WHERE MATCH(value) AGAINST('+A +B' IN BOOLEAN MODE); will return only one row with space separated values:
1 A B C

Is there a way to make MATCH...AGAINST use other word separators? I tried to use LIKE and it was too slow.

Upvotes: 0

Views: 82

Answers (1)

Nathan Hawks
Nathan Hawks

Reputation: 587

You will probably want to alter your app and schema just a little bit to solve this problem. You have two tasks:

Task 1: Transform your existing data

Assuming you need to keep the source data unchanged:

Step 1: Add a field to your schema, "searchFriendly", same datatype as the source data.

Step 2: Write a script to transform the data you already have. Get the whole data set and do string replaces to get spaces.

Step 3: Save that transformed data to the new searchFriendly field.

Task 2: Modify the app so that all future database save/update's on this data, also perform the transformation and save that data as well.

Step 1: Find the part of the app that saves these records.

Step 2: Before actually writing the data to the database, perform the transformation.

Step 3: Add the transformed data to your API call to save/update the record, under the searchFriendly field.

Upvotes: 1

Related Questions