how to optimize url_rewrite query in Magento 1.9

Question

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.

• Mage2 Developer: Please check in google site speed test. its give you more idea.
• inrsaurabh: I checked on google page speed but nothing special there this can we optimize this longest query

Answer 1

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?

• inrsaurabh: its hosted on aws, hardware will be not a issue for this website, its auto scaled
• inrsaurabh: In magneto how we can optimize that longest query , any suggestion
• manu: 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: 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

Answer 2

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)
{
 $select->joinLeft(
 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)
{
 $select->joinLeft(
 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/%']) . ')';
 $select->where($conditions);
 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.