Magento – Product list slow (category and searchresult pages), what can i change/remove

magento-1.7

I'm fighting with the product list view, it is loading too slow.
When pages are cached, speed is OK (stupid to tell this, but maybe this info is needed ;-))

Mysql queries: +- 800 per category or new search request (I think this is that much because I have 190 000 products in my shop).
All settings are OK (what I can see with mysql tuner and other stuff like profiler).

I think the problem is somewhere in the attributes that needs to be loaded in the list.html. I have seen on other people posts there is a problem with $attrs = $_product->getTypeInstance(true)->getConfigurableAttributesAsArray($_product); I cannot find where that code is located and if I need to change that file to get my custom attributes faster.

Is there a easy way to find the attribute or code that is slowing down list.phtml?

Maybe 1 file where I can remove stuff and recheck the load time after I removed stuff?

I have also tried to remove blocks in the local.xml but that was not fixing the load time.

I have changed the "new product" stamps, so it won't load "new" but my custom attribute (% a product is dropped, just a textfield no calculating with php just plain text).

I load that custom attribute and load different "buy now" buttons.

depending on a custom attribute, 
if (attribute = "ID") {
 then load this button 
}
if (attribute ID = 2) {
 load this button
}

So I just load 2 custom attributes, maybe attributes are not the problem. But it needs to be somewhere. The thing I know for sure is when I add more products the loading time will be longer.

Suggestions are welcome, thanks.

Best Answer

You can use Aoe_Profiler or the default profiler (Nice article about that on Inchoo) to analyse whats happening on your page. It might very well be an issue in your template.

Also make sure the attributes you want to use in the list.phtml are added to the flat product index. You can do this on the attribute edit page. enter image description here

And turn on flat category and product indexes on System > Configuration > Catalog. This will definitely speed up your list page when caching is not turned on.