4

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...

Improve this question
7
  • I had the same issue. It's not a Magento bug (it's just inefficient when you have a large catalog). In my situation I've done the following: rewrote the navigation part so it pulls all the data needed in 1 go (using the collection) and I also had a bottleneck with the network between the web server and the MySQL server (fixed that too). All good now.
    – FlorinelChis
    Commented Mar 3, 2013 at 18:42
  • Also, if the MySQL server is not properly tuned (query cache, etc) or it doesn't have decent resources then it will be slow.
    – FlorinelChis
    Commented Mar 3, 2013 at 18:44
  • Hi @FlorinelChis, thanks for your quick response! It is likely that I could speed things up a bit by throwing more resources at my MySQL hardware, but I'm trying to just reduce the number of calls made to the database as a first attempt... I'm not sure exactly why I keep seeing this database query run when Magento should be cacheing the entire top menu block. Could you elaborate on what you changed to pull the data needed in 1 go? I'm not sure how that would be different from the original query...
    – kimbo6365
    Commented Mar 3, 2013 at 20:12
  • if that query is slow, before actually caching the block a lot of requests will pile up (because the first query takes too long, the next request can't see the cache which is waiting for the query to finish and will trigger another identical query) and cause the load to get to high levels.
    – FlorinelChis
    Commented Mar 3, 2013 at 21:19
  • Oh its definitely a bug. There was a regression at some point between 1.6 and 1.7 that led to it. The collection cache doesn't work as it should for the category listing on both the top nav and layered nav. We've had to implement fixes on dozens of stores after people upgraded.
    – Ben Lessani
    Commented Mar 4, 2013 at 9:17

2 Answers 2

Reset to default
1

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.

Improve this answer
0

This might help: http://www.magentocommerce.com/bug-tracking/issue/index/id/580 There you will find the information how to change the code.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.