Magento Freezing because of SQL Query

Question

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?

Answer 1

That looks like a query for building your category navigation menu. The query itself shouldn't be so slow since it uses the category flat table.

If the category index or the rewrite index were rebuilding at that time that could lock the tables and slow down the query. It also might be that the index rebuilding hangs for whatever reason and locks the tables longer as usual. In such cases it's usefull to check that Magento indexes are uptodate and no rebuilding process is running.

It's also good to check the database indexes to ensure that the join can run whithout doing full table scans. If the query is from standard Magento, indexes should be set correctly.