Fulltext Search gives too many results

Hi, Im new to the forum, I had a search to see if anyone had already answered my question but couldnt find anything.

Im trying to write a simple search engine using a ProductName column to search on.

Example Data

Column pdt_name:
widget
megawidget
nice widget
blue widget



Here is my query,

SELECT pdt_name, MATCH(pdt_name)
AGAINST ('+widget +blue') as Relevance
FROM products WHERE MATCH
(pdt_name) AGAINST('+widget +blue'
IN BOOLEAN MODE)
HAVING Relevance > 0.2 ORDER
BY Relevance DESC;


The problem is, it is returning too many results. If I search on "Blue Widget" I only want one result, it is instead bringing back many results albeit with a relevance score, although this score does not give me an obvious cut off number, eg those below 5 are no good.


Any advice will be greatly appreciated.

have you tried LIKE?

i mean, fulltext searching against that column seems like overkill

Thanks for the reply. The actual table has around 300,000 rows and the data is much longer eg "Black Cast Iron Fireplace Surround complete with fixing bracket"

I thought LIKE would be slow as I would have to use LIKE '%iron%' AND LIKE '%black%' which, correct me if im wrong, but this wouldnt use the index.

Also, using LIKE I have the problem of "megawidget" being returned on a search for '%widget%' (im not sure if the fulltext method will elimiate this, I havent got that far yet).

Again, thanks for your response

sheeeeit, man, i just answered the question you asked

you shoulda asked the real question in the first place

i am ~so~ tired of people asking the wrong questions and then not being happy with the answers they get

nothing against you personally, man, you're just the Nth guy to pull this stunt

To be honest you didnt answer my question, my question was about Fulltext searches and my example was there to illustrate in simple terms what my problem was.

I dont consider my question to be a "stunt", if you dont want to help that is fine.

actually your example was poor since you seemed to indicate that there were a single or couple of words in your column, rather than the second example where your terms are wrapped in a larger amount of text. that would have been better in your first post.

also believe that in your search you have searched for BLUE or WIDGET i.e. give me any blues, any widgets and of course any blue widgets.

if you want only BLUE WIDGET your + symbol belongs only at the beginning of the search and not in front of the second term.

so you likely want:

+ 'blue widget'

rather than '+widget +blue'