So I've been tasked with making a bestsellers box, yet I've heard a horror story about the bestsellers box in a previous site taking down the site because of the extensive load.
First, I should mention that this site has quite a bit of products, somewhere in the thousands or ten thousands.
Ive seen extensions and code snippets that do bestsellers, and it seems to make use of something like this:
$collection = Mage::getResourceModel('reports/product_sold_collection') ->addOrderedQty() ->setStoreId($storeId) ->addStoreFilter($storeId) ->setDateRange($sellDate['startdate'], $sellDate['todaydate']) // ->addUrlRewrite() ->setOrder('ordered_qty', 'desc') ->setPageSize($this->getModel()->getHomepageProductsLimit()) ->setCurPage(1) ->setOrder('ordered_qty', 'desc');
I looked into that and it seems like whats going on is that its going through all the products, and getting ordered_qty from those products.
I also noticed that there were some cool tables called sales_bestsellers_aggregated_monthly / sales_bestsellers_aggregated_daily / sales_bestsellers_aggregated_weekly that get populated by a cron task.
I thought it would be cool to make use of these tables, but the problem with that is A) there is no category data and B) its monthly/daily/weekly rather than all time. That latter may not be such an issue, but its something to be considered.
I guess what I am looking for is some thoughts on how to approach this in a way where its not going to take down the site. The site will be behind varnish, but I heard even behind varnish, the last site was taken down by bestsellers.