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')
            ->setDateRange($sellDate['startdate'],          $sellDate['todaydate']) //
                            ->setOrder('ordered_qty', 'desc')
            ->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.


  • I think what I am going to do is join catalog_product_category with this bestsellers monthly table and use that. Seems like a good way of going about it. I believe the client can be convinced this is the best way to go. – Bryan Ruiz Mar 22 '13 at 4:35
  • 1
    If the query runs reasonably fast against your production db, I would imagine you'd be ok querying the non-aggregated db tables in your block. I'd just be sure to have a decent cache lifetime for that block. – kalenjordan Mar 22 '13 at 12:41
  • please add and contribute to this question magento.stackexchange.com/questions/105698/… and the code that is being created here github.com/seansan/SNH_Addorderqty – snh_nl Mar 11 '16 at 15:13

The collection shown is actually the Magento-way of doing this. (Sorting once is enough, of course ;) ).

It does not run against all products, it is Magento's way to implement a database query using collections. The "Magento-way" doing the join you want would be the same (pseudo-code):

$collection = Mage::getModel('catalog/product')->getCollection()->join('catalog_category_product as cpp1')->where(...);

You can see the executed SQL-statement:

echo $collection->getSelect();

Ask if u need more help.

  • wouldnt it have to get ordered_qty from all products in order to sort it descending? I dug in the code, and quickly looking at it, it looks like its loading the products, but I could be wrong. – Bryan Ruiz Mar 26 '13 at 0:07

This will give you something to work with and pulls the top 50 best sellers. I have noticed that daily, monthly, yearly actually don't calculate properly in Magento/DB - not sure which one and they are "ALL TIME" which can be problematic. Anyway I know this is a late reply but for future reference for people who need to query best sellers, this should help. You can replace product_name etc. with * if you like. I just needed the following rows.

SELECT product_name, entity_id, product_id, sku, url_path, small_image, SUM( qty_ordered ) AS total
    FROM sales_bestsellers_aggregated_monthly, catalog_product_flat_1
    WHERE store_id =0
    AND entity_id = product_id
    GROUP BY product_name
    ORDER BY total DESC 
    LIMIT 50
  • Oh yeah, you can also change sales_bestsellers_aggregated_monthly to sales_bestsellers_aggregated_daily or sales_bestsellers_aggregated_yearly, but as mentioned they all give the same result for me... – try this Feb 12 '14 at 23:41

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.