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
    Commented Mar 22, 2013 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. Commented Mar 22, 2013 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
    Commented Mar 11, 2016 at 15:13

2 Answers 2


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
    Commented Mar 26, 2013 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
    Commented Feb 12, 2014 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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