[SOLVED] MySQL full-text search using match against return wrong result set

MySQL full-text search using match against return wrong result set

I have the following query:

SELECT * FROM products WHERE is_active = 1 AND MATCH (full_text) AGAINST ('М 1050' IN BOOLEAN MODE)

This query returns two records, here's the full_index column contents of both:

; 013 001 106 ; Ъглошлайф SPARKY M 1050 HD ; 1050W,10000об.,ф125мм ; SPARKY ; 69 ; ; 040 070 1050 ; Вложка дванадесетограм удължена ; 1/4" 5мм FORSE /5295005/ ; FORCE ; 77 ;

But I want only the first row, I don't need the second one. What's wrong?

Solution

In addition to the answer provided by user144590; M is not, by default, considered a word.

From the docs

Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is three characters for InnoDB search indexes, or four characters for MyISAM. You can control the cutoff by setting a configuration option before creating the index: innodb_ft_min_token_size configuration option for InnoDB search indexes, or ft_min_word_len for MyISAM.

UPDATE

Without changing the minimum length I guess you'd have to replace (in the column data) the space in the text 'M 1050' with another word character like '_' and match against 'M_1050' (in the query), or run a much slower LIKE '%M 1050%' condition.