Baobaz Blog

Ever had a need to get most visited products within Magento? Most solutions and modules available use a reports/product_collection and addViewsCount() method for this. It does the job until you are in a need for performance or want to enable flat product's catalog.

Flat catalog issue

What is the problem? Using flat catalog change the way how products are read from database. Instead of complex query with multiple joins to get attributes of product build in EAV model, with flat catalog you need no joins to get attributes. And you do query different table.

While for Mage_Catalog catalog/product_collection it was kept in mind that there exists option of flat catalog for Magento, it was forgotten within Mage::getModel('reports/product_collection')->addViewsCount() method. Though you can fix this issue quite easily rewriting collection class, I would like recommend something different.

Performance problem

Mage_Report to get for you most visited products counts occurences for product view event within report_event table. This table is used to store 6 types of events and grows in size very fast. Having a website with 1000 views per hour, report_event table with 400 000 of records, any query using this table was a site performance killer.

Solution

The true problem of using reports/product_collection for calculating visit counts per product is that we mix up two things - events and reports. Solution for this I have chosen is creation of another table with calculated values of views for each product, table which is populated with cron based task making calculation using report_events, and packing this all together into one module.

Turning on modification of products views calculation and flat product catalog in this particular case decreased load of database 10 times.