Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.
Solved
MYSQL FullTextSearch Doesnt Return for all matches
Posted on 2013-01-04
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
3
7 Comments
I'm assuming this is a very small table? Please see the latter paragraphs at http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html. To quote from there:
If you want a simple keyword search use boolean mode see http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html. Basically your match clause will be something like:
match(content) against ('+software' in boolean mode);
Every correct word in the collection and in the query is weighted according to its significance in the collection or query. Consequently, a word that is present in many documents has a lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Conversely, if the word is rare, it receives a higher weight. The weights of the words are combined to compute the relevance of the row.
Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results.
If you want a simple keyword search use boolean mode see http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html. Basically your match clause will be something like:
match(content) against ('+software' in boolean mode);
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.
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.
Featured Post
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month10 days, 13 hours left to enroll
618 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.