I am trying to optimize of website, currently my website is very slow it took almost 13 seconds in first load than it drop to 5 seconds , all images are optimized and css and js are merged.

Executed 199 queries in 3.1630139350891 seconds
Average query length: 0.015894542387382 seconds
Queries per second: 62.914676977038
Longest query length: 3.0591471195221

Longest query:

SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `mg_catalog_category_flat_store_5` AS `main_table`
 LEFT JOIN `mg_core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.store_id = 5 AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.include_in_menu = '1') AND (main_table.is_active = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC  

135,305 rows approx are there in core_url_rewrite table

On Home page swatches are enabled on some SE posts i found that some time swatches make website a bit slow but i don't want to disable them.

  • Please check in google site speed test. its give you more idea. – Yogesh Mar 30 '18 at 13:33
  • I checked on google page speed but nothing special there this can we optimize this longest query – inrsaurabh Mar 30 '18 at 13:35

Can you leave is some informations about: - used hardware - number of products

Handling 135,305 Rows in core_url_rewrite Table should be no problem. before i fixed a bug in url indexer, my core_url_rewrite table had about 4.000.000 rows...

the longest Query: i think this is building the shop navigation?

  • its hosted on aws, hardware will be not a issue for this website, its auto scaled – inrsaurabh Mar 30 '18 at 13:53
  • In magneto how we can optimize that longest query , any suggestion – inrsaurabh Mar 30 '18 at 13:56
  • sure :) 1) localize the position in magento core, where the query is execute, overwrite this function with a custom module. and add the query result into cache. Use Category save after event, to clean it 2) make sure, that your whole database is stored in memory of your mysql-application server (i think you have to assign them about 8GB of ram), not the indexes of the mysql tables. I'not sure if this query is using a mysql index. i think it doesn't 3) enable master/slave setup in magento, that reading queries are gone to a slave, or several slaves – manu Mar 30 '18 at 14:05
  • 4) The Magento Categorytree is loaded in an phtml File. You can also uncomment this line and load the categories on your own. Currently i don't know, why magento needs to check core_url_rewrite table, when loading a category. The Category iteself has an url attribute, so i dont understand the need of joining core url rewrite – manu Mar 30 '18 at 14:10

I've faced the same issue and I changed the method joinTableToSelect on Mage_Catalog_Helper_Category_Url_Rewrite

From this:

public function joinTableToSelect(Varien_Db_Select $select, $storeId)
        array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
        'url_rewrite.category_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('like' => 'category/%')),
        array('request_path' => 'url_rewrite.request_path'));
    return $this;

To this:

public function joinTableToSelect(Varien_Db_Select $select, $storeId)
        array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
        'url_rewrite.category_id=main_table.entity_id', [ 'request_path' => 'url_rewrite.request_path' ]);

    $conditions = 'url_rewrite.url_rewrite_id IS NULL OR (url_rewrite.is_system=1 AND ' .
                  $this->_connection->quoteInto('url_rewrite.store_id = ? AND ',
                      (int)$storeId) . $this->_connection->prepareSqlCondition('url_rewrite.id_path', ['like' => 'category/%']) . ')';


    return $this;

It worked like a charm.

Basically, it is doing the same thing but instead of adding the conditions on JOIN LEFT block, it is adding as where conditions.

I've tested it with category and product flat tables enabled, if you don't use it, you'd better test it a little more.

Also, it is good to remember that you shouldn't change the core files, you can create a module to extend this Mage_Catalog_Helper_Category_Url_Rewrite or you can add this file into local directory and just change this method.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.