Magento: Optimize URL rewrite frontend select for categories

Explanation.md

After I have seen that some 3rd party modules duplicate the core_url_rewrite table to store there additional category rewrite information in it (1), I came to the conclusion that it should be save to remove the joinLeft() in the flat table classes.

Instead of the joinLeft() you simply refer to the column main_table.url_path as request_path in the tables catalog_category_flat_store_[StoreId]. Please see the new methods.

Downside: To get the correct rewrites in the frontend you need to run the reindex for catalog_category_flat. During this reindex the correct rewrites will be inserted into the column catalog_category_flat_store_X.url_path.

Footnotes:

(1) the join with the core_url_rewrite takes for large product collection really long when you only need the correct rewrite for the categories. This can save per request around ~0.Xsec.

Btw: in Mage_Catalog_Block_Seo_Sitemap_Product::_prepareLayout() the collection is loaded without any join on core_url_rewrite so each product listed will create a select like:

SELECT `core_url_rewrite`.* FROM `core_url_rewrite` WHERE (`core_url_rewrite`.`id_path`='product/158') AND (store_id IN(0, '2')) ORDER BY `store_id` DESC LIMIT 1

Mage_Catalog_Helper_Category_Url_Rewrite.php

<?php

class Mage_Catalog_Helper_Category_Url_Rewrite  {
  
  // Magento >= 1.8
  // original source: https://github.com/Zookal/magento-ce/blob/magento-1.8.1.0/app/code/core/Mage/Catalog/Helper/Category/Url/Rewrite.php#L70
  
    public function joinTableToEavCollection(Mage_Eav_Model_Entity_Collection_Abstract $collection, $storeId)
    {
        $collection->getSelect()->columns('main_table.url_path as request_path');
        return $this;
    }

    public function joinTableToCollection(Mage_Catalog_Model_Resource_Category_Flat_Collection $collection, $storeId)
    {
        $collection->getSelect()->columns('main_table.url_path as request_path');
        return $this;
    }

    public function joinTableToSelect(Varien_Db_Select $select, $storeId)
    {
        $select->columns('main_table.url_path as request_path');
        return $this;
    }  
  
}

Mage_Catalog_Model_Resource_Category_Flat.php

<?php

class Mage_Catalog_Model_Resource_Category_Flat extends  ... { 

    // Magento < 1.8
    // original source https://github.com/Zookal/magento-ce/blob/magento-1.7.0.2/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php#L258
    protected function _loadNodes($parentNode = null, $recursionLevel = 0, $storeId = 0)
    {
      // 8< -- 8< --- 8<
              $select = $_conn->select()
            ->from(
                array('main_table' => $this->getMainStoreTable($storeId)),
                array('entity_id',
                    new Zend_Db_Expr('main_table.' . $_conn->quoteIdentifier('name')),
                    new Zend_Db_Expr('main_table.' . $_conn->quoteIdentifier('path')),
                    'is_active',
                    'is_anchor',
                    'main_table.url_path as request_path' // ADDED column
                ))
            // removed the joinLeft() to table core_url_rewrite
            ->where('main_table.is_active = ?', '1')
            ->where('main_table.include_in_menu = ?', '1')
            ->order('main_table.position');
      // 8< -- 8< --- 8<
    }

    // original source https://github.com/Zookal/magento-ce/blob/magento-1.7.0.2/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php#L1196
    public function getParentCategories($category, $isActive = true)
    {
      // 8< -- 8< --- 8<
             $select     = $read->select()
            ->from(
                array('main_table' => $this->getMainStoreTable($category->getStoreId())),
                // added: main_table.url_path as request_path')
                array('main_table.entity_id', 'main_table.name', 'main_table.url_path as request_path')
            )
            // removed the joinLeft() to table core_url_rewrite
            ->where('main_table.entity_id IN (?)', array_reverse(explode(',', $category->getPathInStore())));
       // 8< -- 8< --- 8<
    }
}

Mage_Catalog_Model_Resource_Category_Flat_Collection.php

<?php

class Mage_Catalog_Model_Resource_Category_Flat_Collection extends ... {

    // original source https://github.com/Zookal/magento-ce/blob/magento-1.7.0.2/app/code/core/Mage/Catalog/Model/Resource/Category/Flat/Collection.php#L311
    public function addUrlRewriteToResult()
    {
        // removed the joinLeft() to table core_url_rewrite
        $this->getSelect()->columns('main_table.url_path as request_path');
        return $this;
    }


}

After your comment on mklooss/Loewenstark_UrlIndexer#1 i thought about a new method to save the category urls, like product url (own hidden Attribute), so we did not need a join/leftjoin for this cases (if flat table is active for categories).

also finding the current product url (Mage_Catalog_Model_Url::getProductRequestPath).
when 2 Products have the same url_key magento create a every reindex process a new url for on of this products

and the Sitemap Module have to be refactore. - MemoryLeak etc. on 1.5 Mio Article the sitemap need like more than an hour and use more then 6 GB RAM
https://github.com/Zookal/magento-ce/blob/magento-1.7.0.2/app/code/core/Mage/Sitemap/Model/Sitemap.php#L171
https://github.com/Zookal/magento-ce/blob/magento-1.7.0.2/app/code/core/Mage/Sitemap/Model/Resource/Catalog/Product.php#L170

I added also a Database Index into the core_url_rewrite Table:
https://github.com/mklooss/Loewenstark_UrlIndexer/blob/master/app/code/community/Loewenstark/UrlIndexer/sql/urlindexer_setup/install-1.0.0.0.php#L16

Google only accepts sitemaps with a max uncompressed size of 50MB. Your 1.5Mio products should generate a bigger file.

So for your solution you must use a refactored sitemap module ;-) Hint: Mage_Core_Model_Resource_Iterator::walk

Sua solução me ajudou! Obrigado. \o/