PROBLEM
From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column.
Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on.
SOLUTION
What you really need is a single FULLTEXT index with all 3 columns
ALTER TABLE articles ADD FULLTEXT content_title_keywords_ndx (content,title,keywords);
Only then can you say
match(content,title,keywords) against ('cats' in boolean mode)
I have suggested making compound FULLTEXT indexes before
GIVE IT A TRY !!!
Try This:
DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'
SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT.Rank
FROM Patient_Ex PAT
INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT
ON PAT.Id = CT.[Key]
ORDER BY CT.RANK DESC
Explanation:
The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria.
Update:
Try adding weights to your keyword and see if it changes things:
DECLARE @SearchString VARCHAR(200)
SET @SearchString ='ISABOUT (Adam WEIGHT (.8), West WEIGHT (.4), 60608 WEIGHT (.2) )'
SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT .RANK
FROM Patient_Ex PAT
INNER JOIN CONTAINSTABLE(Patient_Ex,*,@SearchString,LANGUAGE N'English' ) AS CT
ON PAT.Id = CT .[KEY]
ORDER BY CT.RANK DESC;
GO
If the above doesn't works either try rebuilding your Catalog if you have very few rows in database as people have reported wrong rank outputs with databases having less no of rows while the ranking working fine with database having large no. of rows,another workaround is to create dummy table in your database and index it in your catalog.
Please read this if you want to learn more about how ranks are calculated.
Source : 1,2
Best Answer
That is because "in" is a very frequent word in the English language, so it is eliminated as a "stopword".
You could use the
simpletext search configuration, but I suspect that full text search is not the right tool for this. Trigram indexes work better for substring searches, but nothing will really work well for strings as short as two characters.