magento/hackathon_january_2014 — pull request #33 by genisd (2014-02-01 17:29:59+00:00)
Title: Index in core url rewrite table

added index which will improve query performance inside core_url_rewrite tables.

in fact such an index was previously present as well. Can be found inside the 0.7.46 -> 0.7.47 migration (mysql4-upgrade-0.7.46-0.7.47.php).

opted not to remove other index, as composite indexes differ by the order of the columns in which they have been added

This index will improve the following frequent query:

SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `main_table`.`thumbnail`, `main_table`.`show_from_price`, `main_table`.`from_price_filter`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_6` 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='6' 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;