Currently this query is slowing the entire site down:
Spawns in irregular times:
SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`,
`main_table`.`is_active`, `main_table`.`is_anchor`,
`url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS
`main_table` LEFT JOIN `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 = 1 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
Freezes the entire site and the process says, creating sort index, or sending data. Meanwhile CPU spikes to 100%...
Anyone knows what triggers this, and/or how to resolve this?