Magento Search: Fulltext & Combine (Like and Fulltext) in relation to Relevance

Question

I'm trying to understand the Magento search index, from what I've read the "Like" search type builds no relevance, and as such having that enabled and sorting by relevance is pretty pointless.

System → Configuration → Catalogue → Catalog Search → Search Type

I read this post on SO: Magento Search 'relevance' Value is Always 0

The answer (to which the bounty was awarded) by Vinai mentions some interesting points:

1. Short answer: the relevance is only used in full text search mode.

2. The way Magento builds the search index isn't very intuitive, I recommend a look at the table catalogsearch_fulltext. Then tune the attributes you want to use in the search by adjusting the Used in Quick Search property for them. This setting can be found under Catalog > Attributes > Manage Attributes. Then reindex the catalog search index. I also recommend clearing out the catalogsearch_result table after you adjust the attributes.

3. ColinM comments:

After you truncate catalogsearch_result you need to also UPDATE catalogsearch_query SET is_processed=0 to force queries to be re-evaluated or else you get blank results

My questions are as follows:

1. Is it safe to truncate the catalogsearch_* tables (fulltext,query & result)
2. Does the Combine (Like and Fulltext) catalog search type utilize the relevance index?

Any input would be much appreciated.

Answer 1

The basic answer is "Magento search doesn't work well in either mode, get another search engine system." I have done all the above except truncating the query and result tables. If you want to waste a lot of time like I wasted a lot of time trying to make a minimal function search system work, feel free to pursue anything you want, but Sisyphus didn't have much return on effort either.

Modules are available for Sphynx, Lucene, Solr. In this instance, Free is lost sales so go for one that implements one of the above and doesn't try to patch the stock Magento search.

Anything 3rd party will give you 80% relevance or better and actually let you sort by relevance.

Stock Magento is in the 20-40% range for return results and that's as good as it gets. Also the random return is based on product order, not relevance order for LIKE so 500 returns probably means the relevant ones are 2/3 the way through.

When I tried it, the Mysql "Fulltext" never added any actual relevance (separate from relevance rating) to the search and defaults to not searching for words that are shorter than 4 characters (no stop-word functions, we'll just ignore everything so that actual 3 character part name NEVER is found).