Slow log query groupscatalog showing often

[Auroraboreali-s]

Hi there,

Recently I was looking at my slow log query and I've noticed that the groupscatlog is showing up very often with this query:

SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '67') AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '67') AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='1' AND core_url_rewrite.id_path LIKE 'category/%')
 INNER JOIN `groupscatalog_category_idx` ON groupscatalog_category_idx.catalog_entity_id=e.entity_id AND groupscatalog_category_idx.group_id=0 AND groupscatalog_category_idx.store_id=1 WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('503', '470', '239', '515', '383', '399', '611', '395', '397', '496', '404', '485', '417', '398', '401', '400', '402', '403', '396', '520', '374', '501', '375', '514', '379', '376', '513', '516', '517', '455', '518', '519', '385', '474', '456', '475', '408', '406', '410', '486', '612', '487', '488', '499', '489', '490', '495', '491', '492', '493', '494', '581', '582', '583', '584', '585', '409', '372', '589', '590', '609', '591', '592', '593', '594', '595', '596', '597', '598', '599', '600', '601', '602', '240', '586', '521', '607', '522', '588', '523', '524', '525', '526', '528', '529', '530', '531', '527', '532', '244', '603', '534', '608', '535', '587', '536', '537', '538', '539', '540', '541', '542', '543', '544', '545', '533', '569', '570', '571', '572', '573', '241', '407', '381', '242', '380', '546', '613', '547', '548', '549', '550', '551', '552', '553', '554', '245', '606', '558', '614', '559', '560', '561', '562', '563', '564', '565', '566', '557', '246', '555', '556', '605', '16', '580', '19', '22', '23', '615', '24', '25', '26', '27', '28', '18', '481', '21', '20', '29', '33', '31', '32', '36', '442', '37', '34', '449', '461', '441', '35', '30', '166', '578', '579', '504', '506', '568', '508', '567', '507', '444', '448', '446', '454', '450', '463', '452', '505', '476', '574', '610', '366', '367', '413', '414', '14', '502', '13', '369', '511', '435', '387', '473', '368', '577', '436', '576', '371', '370', '469', '434', '428', '430', '429', '458', '468', '483', '388', '477', '478', '479', '390', '500', '465', '466', '467', '464', '440', '411', '438', '433', '416', '405', '394', '412', '391', '437', '439', '457', '480', '342', '330', '331', '332', '333', '334', '335', '336', '337', '338', '339', '340', '341', '343', '386', '393', '421', '420', '423', '424', '471', '482', '422', '389')) AND (`e`.`entity_id` NOT IN('26', '34', '35', '36', '166', '366', '367', '367', '390', '391', '394', '405', '411', '413', '413', '414', '414', '416', '417', '422', '433', '434', '436', '437', '438', '439', '440', '449', '452', '457', '458', '463', '464', '465', '465', '466', '466', '467', '467', '468', '478', '479', '480', '483', '485', '500', '526', '533', '539', '569', '570', '571', '572', '573')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1')

AFF: 181

Any thoughts on how to solve it?

I'm using magento 1.9 and the latest groupscatalog2 version.

[Vinai]

Can you please prefix the SELECT with EXPLAIN and post the resulting query plan?

EXPLAIN SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at...