11

I was reading through the list of tables that are ok to truncate (https://stackoverflow.com/questions/12205714/list-of-tables-to-safely-truncate-in-magento) and I didn't see

report_viewed_product_index

The table is huge and it takes a very long time to restore the database. Is it safe to truncate this data or at least remove the oldest data?

  • 1
    Interesting question, btw. I wondered the same some time ago :) – Anna Völkl Nov 11 '14 at 11:18
14

As far as I can see/know this table is included in the event log_log_clean_after.

If you look under the file app/code/core/Mage/Reports/etc/config.xml you will see the following snippet.

<events>
    <log_log_clean_after>
        <observers>
            <reports>
                <class>reports/event_observer</class>
                <method>eventClean</method>
            </reports>
        </observers>
    </log_log_clean_after>
</events>

This method simply cleans all the report events and then the product viewed and compared tables.

public function eventClean(Varien_Event_Observer $observer)
{
    /* @var $event Mage_Reports_Model_Event */
    $event = Mage::getModel('reports/event');
    $event->clean();

    Mage::getModel('reports/product_index_compared')->clean();
    Mage::getModel('reports/product_index_viewed')->clean();

    return $this;
}

If you make sure you have the logClean cron setup then the reports should also be cleaned up with it.

  • Nice answer, David :) – Anna Völkl Nov 11 '14 at 11:29
11

I put some research in this some time ago as we also had issues with that table. report_viewed_product_index is used for the recently viewed products. If you don't use this feature: Go and truncate :-)

If you use the recently views products functionality, check if your cron is set up correct. The entries for visitors that do no more exist in the log/visitor table should then be removed automatically at the log_log_clean_after event.

The clean method is inherited for Mage_Reports_Model_Resource_Product_Index_Viewed from Mage_Reports_Model_Resource_Product_Index_Abstract where this happens.

/**
 * Clean index (visitor)
 *
 * @return Mage_Reports_Model_Resource_Product_Index_Abstract
 */
public function clean()
{
while (true) {
    $select = $this->_getReadAdapter()->select()
        ->from(array('main_table' => $this->getMainTable()), array($this->getIdFieldName()))
        ->joinLeft(
            array('visitor_table' => $this->getTable('log/visitor')),
            'main_table.visitor_id = visitor_table.visitor_id',
            array())
        ->where('main_table.visitor_id > ?', 0)
        ->where('visitor_table.visitor_id IS NULL')
        ->limit(100);
    $indexIds = $this->_getReadAdapter()->fetchCol($select);

    if (!$indexIds) {
        break;
    }

    $this->_getWriteAdapter()->delete(
        $this->getMainTable(),
        $this->_getWriteAdapter()->quoteInto($this->getIdFieldName() . ' IN(?)', $indexIds)
    );
}
return $this;
}
  • not too bad an answer yourself ;) – David Manners Nov 11 '14 at 11:47
  • Hmm, we have a lot of records with visitor_id is NULL in report_viewed_product_index - it seems that these records will not be removed – Jiří Chmiel Dec 13 '17 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.