Processing... Please wait...

Product was successfully added to your shopping cart.

[Solved] Magento gets stuck when generating Google Sitemap on a large website

I encountered a problem the other day - a complex magento 1.9 website, over 100k SKUs and Google Sitemap generation takes forever and never completes.

It didn't work with cron neither it worked with manual Catalog > Google Sitemap backend generation.

Diving into the code I found that this SQL query took ages to run:

SELECT `main_table`.`entity_id`
          , `url_rewrite`.`request_path` 
FROM `catalog_product_entity` AS `main_table`  
INNER JOIN `catalog_product_website` AS `w` 
  ON main_table.entity_id = w.product_id  
LEFT JOIN `core_url_rewrite` AS `url_rewrite` 
  ON url_rewrite.product_id = main_table.entity_id 
  AND url_rewrite.is_system = 1 
  AND url_rewrite.category_id IS NULL 
  AND url_rewrite.store_id = 1 
  AND url_rewrite.id_path LIKE 'product/%' 
WHERE (w.website_id='1')

This query loads products with rewritten URLs.

Notice category_id IS NULL statement. It turned out that MySQL didn't use index associated with category_id field because we had IS NULL.

With core_url_rewrite table having over 1.5M records the query will take a long time to be processed.

A fix would require removing category_id IS NULL and setting id_path REGEXP '^product/[0-9]+$'.

Copy app/code/core/Mage/Catalog/Helper/Product/Url/Rewrite.php to app/code/local/Mage/Catalog/Helper/Product/Url/Rewrite.php and add this function:

    public function joinTableToSelectPatch(Varien_Db_Select $select, $storeId)
            array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
            'url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND ' .
                $this->_connection->quoteInto('url_rewrite.store_id = ? AND ',
                    (int)$storeId) .
                $this->_connection->prepareSqlCondition('url_rewrite.id_path', array('regexp' => '^product/[0-9]+$')),
            array('request_path' => 'url_rewrite.request_path'));
        return $this;

Then copy app/code/core/Mage/Sitemap/Model/Resource/Catalog/Product.php to app/code/local/Mage/Sitemap/Model/Resource/Catalog/Product.php and change line 72 to:

$urlRewrite->joinTableToSelectPatch($this->_select, $storeId);

Now Google Sitemap generation should take around 10-20s.



If you find this post interesting do not hesitate to sign up for our newsletter and join the 636 people who receive Magento news, tips and tricks regularly.

Thank You!