0

I enabled slow query log for my mySQL to observe the slow queries that take more than 10 seconds to be executed. Then I found only one query that is being logged in the mysql.slow_log table. That is:

SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` 
   ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) 
   AND (`at_is_active_default`.`attribute_id` = '42') 
   AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` 
   ON (`at_is_active`.`entity_id` = `e`.`entity_id`) 
   AND (`at_is_active`.`attribute_id` = '42') 
   AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` 
   ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) 
   AND (`at_include_in_menu_default`.`attribute_id` = '67') 
   AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` 
   ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) 
   AND (`at_include_in_menu`.`attribute_id` = '67') 
   AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` 
   ON (core_url_rewrite.category_id=e.entity_id) 
   AND (core_url_rewrite.is_system=1 
   AND core_url_rewrite.product_id IS NULL AND core_url_rewrite.store_id='1' 
   AND id_path LIKE 'category/%') 
WHERE (`e`.`entity_type_id` = '3') 
   AND (`e`.`entity_id` IN('####HERE ARE SOME IDs####')) 
   AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') 
   AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1')

It also causing high CPU usage. I observed the log time and CPU monitoring curve on that same time and found that. It is being happened after 9/10 hours gap frequently. Can you please help me when this query is called/executed by magento so that I can change any settings that is responsible for executing this query?

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
Nuhil Mehdy
  • 2,424
  • 1
  • 21
  • 23

1 Answers1

0

This can be caused by index catalogue_url

A quick fix is to set all indexing to manual.

Then run this shell script via cron hourly:

php shell/indexer.php --reindex catalog_product_attribute
php shell/indexer.php --reindex catalog_product_price
php shell/indexer.php --reindex tag_summary
php shell/indexer.php --reindex cataloginventory_stock
php shell/indexer.php --reindex catalogsearch_fulltext
php shell/indexer.php --reindex catalog_category_product
php shell/indexer.php --reindex catalog_category_flat
php shell/indexer.php --reindex catalog_product_flat 

It indexes all but urls.

Then run this nightly:

<?php

echo "clearing core_url_rewrite so that indexer can repopulate immediately\n";

require_once "/home/path/to/store/app/Mage.php";
umask( 0 );
Mage::App('default');

$resource = Mage::getSingleton('core/resource');

$writeConnection = $resource->getConnection('core_write');

$query = "TRUNCATE `core_url_rewrite`";

$writeConnection->query($query);

echo "cleared\n";

$start = time();

echo "reindexing catalog_url start \n";

$process = Mage::getSingleton('index/indexer')->getProcessByCode('catalog_url');
$process->reindexEverything();

$finish = time() - $start;

echo "reindexing catalog_url finished in ".$finish." seconds \n";

?>
JaseC
  • 3,103
  • 2
  • 21
  • 22