On Thu, Jan 26, 2012 at 6:15 PM, Linus Pettersson <
linus.petters...@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.
HTH,
Peter
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
