Long running queries presumably from layered navigation about magento-lts HOT 13 OPEN

SELECT `operating_system_idx`.`value`, COUNT(operating_system_idx.entity_id) AS `count` 
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17' 
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 
INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '265' 
INNER JOIN `catalog_product_index_eav` AS `screen_type_idx` ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '39' 
INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41' 
INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54' 
INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309' 
INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164' 
INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377' 
INNER JOIN `catalog_product_index_eav` AS `operating_system_idx` ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND operating_system_idx.store_id = '1' 
GROUP BY `operating_system_idx`.`value`

SELECT `manufacturer2_idx`.`value`, COUNT(manufacturer2_idx.entity_id) AS `count`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `catalog_category_product_index` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = 1 AND
                       cat_index.visibility IN (2, 4) AND cat_index.category_id = '15'
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND
                       price_index.customer_group_id = 0
         INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx`
                    ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND
                       screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '249'
         INNER JOIN `catalog_product_index_eav` AS `screen_type_idx`
                    ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND
                       screen_type_idx.store_id = 1 AND screen_type_idx.value = '37'
         INNER JOIN `catalog_product_index_eav` AS `core_count_idx`
                    ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND
                       core_count_idx.store_id = 1 AND core_count_idx.value = '41'
         INNER JOIN `catalog_product_index_eav` AS `operating_system_idx`
                    ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND
                       operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
         INNER JOIN `catalog_product_index_eav` AS `ram_idx`
                    ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND
                       ram_idx.value = '52'
         INNER JOIN `catalog_product_index_eav` AS `memory_idx`
                    ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND
                       memory_idx.store_id = 1 AND memory_idx.value = '62'
         INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx`
                    ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND
                       primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309'
         INNER JOIN `catalog_product_index_eav` AS `second_camera_idx`
                    ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND
                       second_camera_idx.store_id = 1 AND second_camera_idx.value = '87'
         INNER JOIN `catalog_product_index_eav` AS `megethossim_idx`
                    ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND
                       megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
         INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx`
                    ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND
                       nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
         INNER JOIN `catalog_product_index_eav` AS `manufacturer2_idx`
                    ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND
                       manufacturer2_idx.store_id = '1'
GROUP BY `manufacturer2_idx`.`value`