1

I'm not an expert on FULL TEXT search so I kind of googled around for an SQL query SORTED BY RELEVANCY and came up with this which is somewhat working:

SELECT *, MATCH(fulltext_tags) AGAINST ('+blue' '+red') AS relevance FROM `products` WHERE MATCH(fulltext_tags) AGAINST ('+blue' '+red' IN BOOLEAN MODE) ORDER BY relevance DESC

The problem is, only the first keyword is taken into account. Say I have this Product table

+----------+------------------+
|   name   |  fulltext_tags   |
+----------+------------------+
| Product1 | Blue             |
| Product2 | Red              |
| Product3 | Blue Red         |
| Product4 | Yellow           |
+----------+------------------+

The above query only returns product 1 and 3, it is completely ignoring the second keyword '+red'. Even adding +Yellow as third keyword still ignores it, so it ignores anything past the first keyword...

How do I make it so a query with +blue and +red returns Product1 Product2 and Product3 since at least one or both keywords are figuring in the fulltext_tags field ?

Thanks

Flames
  • 123
  • 1
  • 1
  • 6
  • By default full-text indexing ignores words 3 letters long or smaller. – Barmar Jun 06 '16 at 06:55
  • Please remove the duplicate, this question's answer has nothing to do with the 3 letter keyword "red" used in the query. I specifically stated that even using Yellow it still doesn't work so the problem is not with the 3 letter keyword. – Flames Jun 06 '16 at 13:28

1 Answers1

1

You should use REGEXP '[[:<:]]Red[[:>:]]' for find exact word, so try below query hope it will work and fulfill your requirement.

SELECT * FROM `products` 
  WHERE fulltext_tags REGEXP '[[:<:]]Red[[:>:]]' 
  OR fulltext_tags REGEXP '[[:<:]]Blue[[:>:]]';

You can see DEMO Here.

Update 1

Try This Query, As I can observe you want to do something like this,

SELECT *, 
      MATCH(fulltext_tags) AGAINST ('+blue' '+red')     
      AS relevance FROM `products` 
  WHERE MATCH(fulltext_tags) AGAINST('Red') 
      OR MATCH(fulltext_tags) AGAINST('Blue')
  ORDER BY relevance DESC

See here ONLINE DEMO

Piyush Gupta
  • 2,181
  • 3
  • 13
  • 28