asked on
MYSQL FullTextSearch Doesnt Return for all matches
Hi Guys,
I have this query.
SELECT ItemId,MenuId,SubMenuId,Te
MATCH(Content) AGAINST ('software') AS SearchScore
FROM SubMenuItems WHERE MATCH(Content) AGAINST ('software')
ORDER BY SearchScore DESC
now running it agains a fulltext enabled myisam table.
This is in the content column.
We are located in Central New Jersey. Our support team is also located in Central New Jersey. You will always get a perfect english speaking person on the phone when you call us. We will be able to help you remotely using the software TeamViewer to access your computer and help you with your needs. TeamViewer is extremely safe, and no one can connect to your computer without your permission. You have to initiate a session. Each session has a different random generated password and id, to insure that no one that had access to your computer once, will be able to access it again without new information from you the customer.
The word software is in the text, but is not returned by my query.
if i do this query
SELECT ItemId,MenuId,SubMenuId,Te
MATCH(Content) AGAINST ('person') AS SearchScore
FROM SubMenuItems WHERE MATCH(Content) AGAINST ('person')
ORDER BY SearchScore DESC
it does return the content. WHY? am i totally missing something here?
I have this query.
SELECT ItemId,MenuId,SubMenuId,Te
MATCH(Content) AGAINST ('software') AS SearchScore
FROM SubMenuItems WHERE MATCH(Content) AGAINST ('software')
ORDER BY SearchScore DESC
now running it agains a fulltext enabled myisam table.
This is in the content column.
We are located in Central New Jersey. Our support team is also located in Central New Jersey. You will always get a perfect english speaking person on the phone when you call us. We will be able to help you remotely using the software TeamViewer to access your computer and help you with your needs. TeamViewer is extremely safe, and no one can connect to your computer without your permission. You have to initiate a session. Each session has a different random generated password and id, to insure that no one that had access to your computer once, will be able to access it again without new information from you the customer.
The word software is in the text, but is not returned by my query.
if i do this query
SELECT ItemId,MenuId,SubMenuId,Te
MATCH(Content) AGAINST ('person') AS SearchScore
FROM SubMenuItems WHERE MATCH(Content) AGAINST ('person')
ORDER BY SearchScore DESC
it does return the content. WHY? am i totally missing something here?
DatabasesMySQL ServerSQL
Last Comment
tim_carter
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
I ended up doing like this
SELECT ItemId,MenuId,SubMenuId,Te
MATCH(Content, Code, Text, PageTitle) AGAINST ('+".$rSearchFor."' in boolean mode) AS SearchScore
FROM SubMenuItems
WHERE
MATCH(Content, Code, Text, PageTitle) AGAINST ('+".$rSearchFor."' in boolean mode)
OR
MATCH(Content, Code, Text, PageTitle) AGAINST ('".$rSearchFor."')
OR
Content LIKE '%".$rSearchFor."%'
OR
Text LIKE '%".$rSearchFor."%'
OR
PageTitle LIKE '%".$rSearchFor."%'
ORDER BY
SearchScore DESC
I will still be working on soundex though. thanks
SELECT ItemId,MenuId,SubMenuId,Te
MATCH(Content, Code, Text, PageTitle) AGAINST ('+".$rSearchFor."' in boolean mode) AS SearchScore
FROM SubMenuItems
WHERE
MATCH(Content, Code, Text, PageTitle) AGAINST ('+".$rSearchFor."' in boolean mode)
OR
MATCH(Content, Code, Text, PageTitle) AGAINST ('".$rSearchFor."')
OR
Content LIKE '%".$rSearchFor."%'
OR
Text LIKE '%".$rSearchFor."%'
OR
PageTitle LIKE '%".$rSearchFor."%'
ORDER BY
SearchScore DESC
I will still be working on soundex though. thanks
I wonder why you still need to use the LIKE '%keyword%' pattern. That basically kills the benefits of using a fulltext index as MySQL will now need to do a full table scan every time. Is it because of the minimum number of chars a word must have to be indexed by fulltext and/or certain reserved words and numbers it won't index? I think you should finetune fulltext and know how to work around the limitations instead. For example - fulltext won't allow you to search for "c++". The workaround is to create another column with the same content but with "c++" replaced by "cplusplus", index that column instead of the original, but display the original when showing results. Of course that also requires you to replace the user-entered search params with the same. Last time I checked using a fulltext index will cause MySQL to ignore any index in another column and won't index pure numbers, so one-time when I also needed to add a "where company_id=12345" in my select statement, I added a "companyid12345" in my extra column instead and adjusted my match clause to include it in the boolean search.
Alternatively, you can employ a 3rd party engine like sphinx (http://sphinxsearch.com) instead.
Alternatively, you can employ a 3rd party engine like sphinx (http://sphinxsearch.com) instead.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
well i was just doing a simple search like 'each'. That did not get returned by fulltext.
It's because 'each' is a stopword. See http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html. Also see http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html for how to edit/override the stopword list and other fine-tuning methods.
ASKER
haha ok, thanks so muc
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WHERE Content LIKE '%software%')