Live Chat Software by Kayako |
Knowledgebase: General Q&A
|
Why MySQL full-text search returns incorrect results when it's used on tables containing small amount records ?
Posted by - NA - on 13 September 2005 01:44 PM
|
|
|
For the start, you can turn on/off MySQL full-text search in Admin Area -> Application Setup -> Advanced Setup (option named "Use MySQL full-text search capabilities for Keywords Search"). MySQL full-text search 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. For example, if the word 'test' is present in every row of the table, a search for the word will produce no results. The search result will be empty because the word 'test' is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large datasets, this is the most desirable behavior - a natural language query should not return every second row from a 1GB table. For small datasets, it may be less desirable. A word that matches half of rows in a table is less likely to locate relevant documents. In fact, it most likely finds plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Information source : http://dev.mysql.com/doc/mysql/en/fulltext-search.html | |
|
|
|
Comments (0)