CODEWITHSUNDEEP
magentoquery-optimization
TheVyom
TheVyom

Reputation: 533

Magento: Optimization of db

When I see the code for most viewed products collection, the SQL query shows

 SELECT COUNT(_table_views.event_id) AS `views`, `e`.*, `cat_index`.`position` AS `cat_index_position`
 FROM `report_event` AS `_table_views`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = _table_views.object_id AND e.entity_type_id = 4
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2'
 WHERE (_table_views.event_type_id = '1')
 GROUP BY `e`.`entity_id`
 HAVING (views > 0)
 ORDER BY `views` desc

Looking at the Database the table report_event is a huge and it does not make sense to do a group by or count on this every time.

It would be better to build an aggregate table (almost like a index) for this on a daily basis. I was wondering if any one has ever done this and where is good place to start.

Aggregate Table could be

 SELECT (_table_views.object_id) AS `entity_id`, COUNT(_table_views.event_id) AS `views`, 
 FROM `report_event` AS `_table_views`
 WHERE (_table_views.event_type_id = '1')
 GROUP BY `e`.`entity_id`

On the whole I think it would be good for Magento product as a whole if Varian adds this to the core files.

BTW, I also see another table report_viewed_product_index and why that was not being used to do this query...

Upvotes: 1

Views: 710

Answers (1)

benmarks
benmarks

Reputation: 23205

Fair points for sure. As an open-source, community-oriented product, Magento do listen to their userbase. Feel free to be part of the solution by becoming a contributor! See http://www.magentocommerce.com/blog/be-part-of-the-solution-become-a-magento-contributor/

Also, keep an eye on Magento 2.0 at http://mage2.magentocommerce.com/svn/public/ - you might see some of these improvements on the way.

Upvotes: 2

Related Questions