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.

Answer 2

I had this exact same issue now and resolved it by running this CLI command:

php shell/indexer.php --reindex catalog_category_flat from my public_html folder.

The problem was that the catalog_category_flat index got stuck so each call on the shop got stuck because the query could not process, or took too long to process.

You can check your indexer statues by running:

php shell/indexer.php --status