Magento – 1.7 bug – MySQL hanging due to top navigation menu caching bug

block-cacheMySQLnavigation

I'm running a Magento store with ~45K products and ~3700 categories. I've noticed that my store/server takes huge performance dips multiple times per day as a result of this query: 

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.product_id IS NULL 
   AND url_rewrite.store_id=1 
   AND url_rewrite.id_path LIKE 'category/%' 
WHERE (main_table.is_active = 1) 
   AND (main_table.include_in_menu = 1) 
   AND (main_table.path like '1/2/%') 
ORDER BY `main_table`.`position` ASC;

This problem was also discussed in this question and has been reported as a bug with Magento 1.7.x, but with no real options for solving the problem.

My question is, is it worth it/will it fix the problem by reverting my store to 1.6.2? I don't see how I can overcome this problem otherwise…

Best Answer

It is a bug with the collection cache as mentioned in the comments above. I wouldn't suggest downgrading (I've never even seen that done), but rather just make a few appropriate changes to your code.

I'm on a tablet, so I can't see the Magento source, but I'm sure if you did a diff between your current category navigation class and the previous version, you'll find that Magento likely disabled the block/collection cache.

If you are happy not to have categories marked as `is_active (as that is done in PHP when rendering the tree), then you can just put caching in place at the block level.

Just bear in mind that this query will also take place on your layered navigation too, so the same fix needs to be applied there.

NB. I'd start by deleting your 3000 categories and re-architect your site. Magento won't scale that amount of categories well, nor can I imagine a user browsing the site could digest that many practically without merely resulting to searching.

Related Topic