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?