Slow Queries on Catalogsearch_query – How to Optimize

It looks like a core bug/regression we saw in 1.7 where the block and collection cache weren't working effectively for the navigation menu (catalog/navigation/top.phtml).

You can test by removing it, or just temporarily capture the output into a file with an ob_start and serve it from a static file/memcache.

Also, the hardware you are using doesn't sound huge and looks under specified for the size of the store you have. There is probably an I/O bottleneck there too - SAN storage + congested network = poor performance.

--

As a crude solution, you can adjust the block class for the navigation (dump get_class($this)) in top.phtml to identify it.

This will allow site-wide caching, without the category level caching that the new version invoked. Its also worth removing the is_active class from the tree renderer if you do this to avoid random menu items appearing selected (and implement a JS alternative instead).

public function getCacheTags()
{
  return parent::getCacheTags();
}
public function getCacheLifetime()
{
  return null;
}
public function getCacheKey()
{
  return parent::getCacheKey();
}
public function getCacheKeyInfo()
{
  $shortCacheId = array(
    'CATALOG_NAVIGATION',
    Mage::app()->getStore()->getId(),
    Mage::getDesign()->getPackageName(),
    Mage::getDesign()->getTheme('template'),
    Mage::getSingleton('customer/session')->getCustomerGroupId(),
    'template' => $this->getTemplate(),
    'name' => $this->getNameInLayout(),
  );
  $cacheId = $shortCacheId;
  $shortCacheId = array_values($shortCacheId);
  $shortCacheId = implode('|', $shortCacheId);
  $shortCacheId = md5($shortCacheId);
  $cacheId['short_cache_id'] = $shortCacheId;
  return $cacheId;
}

OBSERVER: auto_add_on_coupon_insertion 4.7411 2 2,388,872 3,145,728

seems to be a big contributor. Default Magento 1.8 does not have such an event observer. As a first step I would suggest going through any installed custom extension to see if disabling it solves the slowness.