MySql Fulltext Search Not Working Properly

@tangtang Thanks but this is not what I want. I need to find the rows that contain BOTH words "liferaft" and "inspection" and from those exclude the ones that contain the word "certificate"

@achatzi

well, you can use the NOT MATCH.

like this

WHERE
    locale = 'en' AND
    MATCH (description) AGAINST ('+liferaft +inspection' IN BOOLEAN MODE) 
    AND NOT MATCH (description) AGAINST ('certificate' IN BOOLEAN MODE)

@tangtang Nope, still doesn't work.

I tried

MATCH (description) against ( '+liferaft +inspection' IN boolean MODE ) 
AND NOT MATCH (description) AGAINST ('certificate' IN BOOLEAN MODE)

and also

MATCH (description) against ( '+liferaft -certificate' IN boolean MODE)

I still get the wrong rows (containing the word 'certificate').

I also tried lowering the score

MATCH (description) AGAINST ('+liferaft +inspection ~certificate' IN BOOLEAN MODE) AS score

but all rows get the same score.

@achatzi

that's odd.

I even tried replicate your case and work well with your first code

the data : https://imgur.com/zFojfQ4

query : https://imgur.com/JslmSzM

is there another settings of your MySQL full-text index ?

another way to make this work is using NOT LIKE

WHERE
 locale='en' AND
	MATCH ( description ) against (
	'+liferaft +inspection -certificate' IN boolean MODE
	)
	AND description NOT LIKE '%certificate%' // but this may case sensitive, you must make another logic for the word with uppercase

with this code if the row have the certificate word will not displayed in result.

@tangtang I know this is odd and it is driving me crazy. I am thinking that there is something wrong with the word 'certificate', like it is a reserved word or something. Whenever I include this in my search it will not work, but if I try other searches everything works as intented.

For example, this query

SELECT
	*,
	MATCH (description) AGAINST ('+fabric +polyester -blue' IN BOOLEAN MODE) AS score 
FROM
	item_translations
WHERE
	locale = 'en' AND
	MATCH (description) against ( '+fabric +polyester -blue' IN boolean MODE ) 
ORDER BY
	score DESC

will only bring me these results

1. Fabric 300D-300D-100Τ Polyester W:60''-Red
2. Fabric Polyester 300D-300D-100T W:60''-White
3. Fabric Polyester 300D-300D-100T W:60''Yellow

but in my data I also have 'Fabric Polyester 300D-300D-100T W:60'' -Blue'

Is there a way to rebuild the fulltext index? Maybe delete everything and import again will fix the issue?

@achatzi

I suggest don't delete all, but add another column, set it to fulltext and copy all data from description to this new column.

and try check the settings in your storage engine for MySQL, settings like innodb_ft_min_token_size and innodb_ft_max_token_size if you are using the InnoDB or ft_min_word_len for MyISAM, this setting affects full index searches

check the sql version too, the version 10.0++ is support all the feature like full index searches

@tangtang I downloaded the db in a local env and tried the same query. It works fine in my local system so, along with your suggestions about the settings, I compared the 2 configs (remote and local) and indeed there were some differences regarding the fulltext indexes.

So I changed the remote config, dropped the table and created again, migrated the data and now everything is working correctly.

I will mark your answer as best, thanks for the help.