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