8

I noticed that I seemingly wasn't able to sort on relevance in a default search. Whether I tried ASC or DESC, the results were always the same and rather poorly relevant at that.

Upon further investigating, I found the addSearchFilter() method in Mage_CatalogSearch_Model_Resource_Fulltext_Collection :

/**
 * Add search query filter
 *
 * @param string $query
 * @return Mage_CatalogSearch_Model_Resource_Fulltext_Collection
 */
public function addSearchFilter($query)
{
    Mage::getSingleton('catalogsearch/fulltext')->prepareResult();

    $this->getSelect()->joinInner(
        array('search_result' => $this->getTable('catalogsearch/result')),
        $this->getConnection()->quoteInto(
            'search_result.product_id=e.entity_id AND search_result.query_id=?',
            $this->_getQuery()->getId()
        ),
        array('relevance' => 'relevance')
    );

    Zend_Debug::dump($this->getData());exit;

    return $this;
}

The result of my data dump shows the results just fine, but the 'relevance' column is always 0.00000. I have not made any changes to the catalog search and this is Magento 1.6.0.

I dumped the actual SQL as well:

SELECT `e`.*, `search_result`.`relevance` FROM `catalog_product_entity` AS `e` INNER JOIN `catalogsearch_result` AS `search_result` ON search_result.product_id=e.entity_id AND search_result.query_id='33'

If anyone else has more experience with calculating relevance I would greatly appreciate direction.

Zachary Schuessler
  • 3,644
  • 2
  • 28
  • 43
  • http://stackoverflow.com/questions/1953715/magento-search-not-returning-expected-results and http://stackoverflow.com/questions/7320757/magento-search-engine-relevance-issues help at all? – Anthony Feb 05 '12 at 00:53
  • Hi Anthony. The problem is moreso that the relevancy just doesn't work.. at all. But now that I look at the Magento demo store, it doesn't sort based on relevancy either (try switching the ASC/DESC). I guess relevancy was just a good idea that never worked? I hadn't thought of that possibility. – Zachary Schuessler Feb 05 '12 at 01:04
  • Anthony if no one else wants to chime in, could you give a brief synopsis of those two answers, and put it in the form of an answer? I'd hate for the bounty to go to waste. – Zachary Schuessler Feb 06 '12 at 16:08

1 Answers1

23

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

Background

In the admin interface you can configure a "Search Type" for Magento.
This setting can be found under System > Config > Catalog > Catalog Search > Search Type

If switched to Fulltext (and after reindexing), and clearing the catalogsearch_query table, Magento will use the MySQL Fulltext Search capability, specifying a WHERE condition in the query as follows: 

...MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) AS `relevance`...

This will return a floating point number that will be used as the relevance value. A plain hit will give you a relevance of 1. If the index contains the search term more then once, it will be given a higher relevance.

Also the boolean fulltext search enables the use of search modifiers like "+this -notThis".
More information on the relevance weighting of the MySQL boolean fulltext search can be found here http://dev.mysql.com/doc/refman/5.1/de/fulltext-boolean.html

If the search mode "Like" is used, the relevance always is 0 (as you noticed).

Indexing

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.

Vinai
  • 14,162
  • 2
  • 49
  • 69
  • 1
    Exactly what I was looking for in an explanation. Cheers good sir. – Zachary Schuessler Feb 07 '12 at 22:34
  • 5
    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. – ColinM Jun 01 '12 at 16:47
  • 1
    After doing that in 1.9 all the relevances are 1.00 despite some of the products having the text more than once. Also some search terms return nothing when I know they are part of the name. – Mike Nov 27 '14 at 15:48