Ruby on Rails

On Thu, Jan 26, 2012 at 6:15 PM, Linus Pettersson <linus.pettersson@gmail.com> wrote:

Hi

I tested to remove the .order(...) part and indeed, the query time goes down to ~100ms. However, it doesn't help to add indices, at least not as I did :)

add_index :categories, :name

add_index :subcategories, :name

Did some more testing and if I keep the .order... but don't join the products table I get a query that runs at about ~55ms. So the bottleneck seems to be the products table.

The query that I'm running looks like this:

Category.eager_load(:subcategories)

             .joins("INNER JOIN products AS p ON resellercategories.id = p.resellercategory_id")

             .order("categories.name ASC, subcategories.name ASC")

(Skipping the gender here...)

What I have is Categories and Subcategories. They are related to each other through a Resellercategories table. Products are related to Resellercategories.

So, the reason that I want to join the products as well is because I only want to show categories and subcategories that actually have some products (there are some empty categories/subcategories still).

So the above query is what we came up with in another thread here in the group.

- Maybe there is a better way to check if a category/subcategory has products without joining the entire products table?

It is possible to add a :counter_cache , but then you need to make sure you
use the proper methods for each product that you add or remove from the
association.

Alternative to the default counter cache (from Rails), you could build your own
logic as in:

* has_male_products
* ...

changing you query to  ...

Category.eager_load(:subcategories).
               where(:has_male_products => true).
               order(...)

Then you would need to set the cache on the appropriate categories in
an after_save on the product you are creating/updating/deactivating/(deleting ?).

Both ideas would probably be faster for querying, but certainly more
complex for making sure that cache is always correct.