I need to render around 2500 categories (includes also nested categories - using ancestry gem) and products count in each of these categories.

Something like the following:

It takes very long time to load the page due to this. I iterate 5 lists (5 level categories in depth) and use the following code to count the products in a category (it always sum the products of the current category and also all nested ones):

<%= Product.includes(:category).where(:id => @filter_product_ids, categories: {:id => Category.find(root['id']).subtree_ids }).count %>

Is there any way how to speed this up? Right now I use only 200 categories and 10 000 products (expecting 500 000) and it takes 30seconds to load the page with only 10 000 products.

Thank you, Miroslav

SOLUTION

Cache counter : https://github.com/magnusvk/counter_culture

You need to look at the SQL that's being run against the database and create indexes for those columns. It looks like you're doing table scans on the products table; an index will speed things up considerably.