Mysql – Searching a text with space in theSql match against Query

MySQLmysql-5.6PHPsubquery

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result.

My MySql Query is:

SELECT count(id) as total
    , MIN(product_price) as min_price
    , MAX(product_price) as max_price 
FROM `products_details` 
WHERE subcat_id = 1026 
    AND MATCH(alternate_name, product_desc, keywords)
    AGAINST ('+moto +g +plus +(>mobile <mobiles)'; IN BOOLEAN MODE)

I have problem with:

AGAINST ('+moto +g +plus +(>mobile <mobiles)'

I want to search for string moto g plus mobile,using the above query. I want to add space before and after the g. Example, moto "SPACE"g"SPACE".

I tried with the 2 following solns, its not working.

[[:space:]]

AGAINST ('+moto +[[:space:]]g[[:space:]] +plus +(>mobile <mobiles)'

and

AGAINST ('+moto +\\ g\\ +plus +(>mobile <mobiles)' ==> I have a space character (Ascii 32) after the \ in this query

Both of them are not working.

Can you please let me know what should be there?

Edit: The value of ft_min_word_len is set 1.

Best Answer

FULLTEXT needs space to be space, not a "letter". That is, you cannot have spaces in a "word".

I would write application code to ignore 'short' words (such as 'g') and end up with

WHERE MATCH(alternate_name, product_desc, keywords)
        AGAINST ('+moto +plus'; IN BOOLEAN MODE)
  AND ( alternate_name LIKE '%moto g plus%'
     OR product_desc   LIKE '%moto g plus%'
     OR keywords       LIKE '%moto g plus%' )

That would be efficient because the MATCH happens first, thereby limiting the number of rows significantly. Then the costly OR and LIKE does not have to touch many rows.

(I left off the 'mobiles' for this discussion.)

Related Question