mysqlfull-text-searchinnodbmysql-5.7exact-match

MySQL Match Against for exact Phrase working partially

I have a table in which I created FullText index in a column called item_desc.

Let's say table contains three records in which column item_desc includes "Sodium Chloride" like following:

  1. Solution Sodium Chloride standard
  2. 5425 Sodium Chloride 100u
  3. QtySodium Chloride solution

I have a following (Match, Against) query which supposed to be return rows by exact matching the records but it is returning only first two rows against Sodium Chloride and doesn't consider the phrase if it is concatenated with another word like QtySodium Chloride.

SELECT * FROM tblhugedata WHERE MATCH(Item_desc) AGAINST('"*Sodium Chloride*"' IN BOOLEAN MODE);

Following LIKE query returns expected results but I want to use only FullText index.

SELECT * FROM tblhugedata WHERE Item_desc like '%SODIUM CHLORIDE%';

Is there anyway to extract such results by match, Against way.

Solution

  • Remove the asterisks. FULLTEXT does not allow for leading wildcards. That is, there is no way to get MATCH to match QtySodium against Sodium.

    I would consider "QtySodium" to be "garbage in" and complain to the provider of the data.

    Here is a kludge that will work in some cases:

    WHERE WHERE MATCH(Item_desc) AGAINST('Sodium Chloride' IN BOOLEAN MODE)
  AND Item_desc LIKE '%SODIUM CHLORIDE%'

    That way, it will efficiently filter down to rows that have either "Sodium" or "Chloride", then check such rows for exactly the substring "Sodium Chloride". That will match your 3 examples, but perhaps not some other examples.