I have a top level category with approximately 100,000 products (and counting). Currently, it takes 20 seconds to load the category page.
I assume what is going on is the query responsible for display the product list requires mysql to load up all 100,000+ products before returning the 9 it needs to display the page.
It would perfectly acceptable to cache the list of products on the category page. It doesn’t really matter if the products are a few hours or even a few days out of sync as long as the product page displays the correct stock information and what not. Is there any way to make magento cache this, so that it does not take so long to load?