I have been working a lot with Magento 1.9. I was changing database to Percona 5.7 (from 5.6) but since then, I have a major issue with "initial" load (for example after database restart).
I have traced, that there is following 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.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
Query is definitely not using key, but why? Does somebody, please, have a suggestion?
I have setted up another shop on Percona 5.7, which has a lot less amount of rows in "core_url_rewrite". I can see, that keys are being used now (unable to paste screenshot)
So it looks like the problem is on the Percona side, perhaps the size of the table goes outside "buffer".