Debugging Magento product filter counts

A handy place to inject a cheeky "error_log" to show the SQL that fetches the counts shown on product filters:

Mage_CatalogIndex_Model_Resource_Attribute::getCount

public/app/code/core/Mage/CatalogIndex/Model/Resource/Attribute.php:76

public function getCount($attribute, $entitySelect)

    {
        $select = clone $entitySelect;
        $select->reset(Zend_Db_Select::COLUMNS);
        $select->reset(Zend_Db_Select::ORDER);
        $select->reset(Zend_Db_Select::LIMIT_COUNT);
        $select->reset(Zend_Db_Select::LIMIT_OFFSET);

        $fields = array('count'=>'COUNT(index.entity_id)', 'index.value');

        $select->columns($fields)
            ->join(array('index'=>$this->getMainTable()), 'index.entity_id=e.entity_id', array())
            ->where('index.store_id = ?', $this->getStoreId())
            ->where('index.attribute_id = ?', $attribute->getId())
            ->group('index.value');

        $select = $select->__toString();

//        $alias = $this->_getReadAdapter()->quoteTableAs($this->getMainTable(), 'index');

// LOG HERE:

error_log(sprint("%sn%sn", __METHOD__, $select), 3, '/tmp/sql.log');

        $result = $this->_getReadAdapter()->fetchAll($select);

        $counts = array();

        foreach ($result as $row) {
            $counts[$row['value']] = $row['count'];
        }

        return $counts;
    }

Tags: magento