2

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.

Improve this question
2
  • g is just too short for fulltext indexes to take it into account. But you can leave it from the match and add another condition (alternate_name LIKE 'moto g plus' OR product_desc LIKE ...). That way you use the fulltext index to get "interesting" rows and then the LIKE filters only these (hopefully few) for exact match.
    – jkavalik
    Commented May 23, 2016 at 20:19
  • Thank you, but the query has to be like what I wanted. The reason is, Your suggestion might work for some situations, and in other conditions, it will not work.
    – Mani
    Commented May 24, 2016 at 4:51

1 Answer 1

Reset to default
6

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.)

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.