search

Vinai / groupscatalog2

Magento extension to enable you to hide categories and products from customers depending on their customer group. This is a Magento 1.6 and newer compatible version of the Netzarbeiter Customer Groups Catalog extension.
139 stars 59 forks source link

Slow log query groupscatalog showing often #127

Open Auroraboreali-s opened 8 years ago

Auroraboreali-s commented 8 years ago

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 commented 8 years ago

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