Processing... Please wait...

Product was successfully added to your shopping cart.

Magento can't generate sitemap xml

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.



Need help with a Magento site? Hire me!


Like the article? Share:


Other articles you might be interested in:

  1. 5 free magento 2 extensions you should try
  2. Magento 2 Features or 5 reasons why you should upgrade
  3. Pagespeed distribution of top 2k Magento websites
  4. WooCommerce beats Magento in Alexa Top
  5. Magento 2 on PHP7 - 25% increase in performance


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

Thank You!