0

I have an select query with about 1M records, I'm working on Magento 1.9 database.

    SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4),
          mt.value + SUM(sup_ap.pricing_value)) AS `value`,
       75                                       AS `attribute_id`,
       `supl`.`product_id`                      AS `entity_id`,
       `cs`.`store_id`
FROM `catalog_product_entity_decimal` AS `mt`
         LEFT JOIN `catalog_product_super_attribute` AS `sup_a` ON mt.entity_id = product_id
         INNER JOIN `catalog_product_super_attribute_pricing` AS `sup_ap`
                    ON sup_ap.product_super_attribute_id = sup_a.product_super_attribute_id
         INNER JOIN `catalog_product_super_link` AS `supl` ON mt.entity_id = supl.parent_id
         INNER JOIN `catalog_product_entity_int` AS `pint`
                    ON pint.entity_id = supl.product_id and pint.attribute_id = sup_a.attribute_id and
                       pint.value = sup_ap.value_index
         INNER JOIN `core_store` AS `cs` ON cs.website_id = sup_ap.website_id
WHERE (mt.entity_id in (select product_id from catalog_product_super_attribute))
  AND (mt.attribute_id = '75')
GROUP BY `entity_id`, `cs`.`store_id`
LIMIT 500

My Explain:

+------+-------------+---------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| id   | select_type | table                           | type   | possible_keys                                                                                                                                                  | key                                                            | key_len | ref                                | rows | Extra                                        |
+------+-------------+---------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
|    1 | PRIMARY     | cs                              | index  | IDX_CORE_STORE_WEBSITE_ID                                                                                                                                      | IDX_CORE_STORE_WEBSITE_ID                                      | 2       | NULL                               |    7 | Using index; Using temporary; Using filesort |
|    1 | PRIMARY     | sup_ap                          | ref    | UNQ_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID_VAL_IDX_WS_ID,IDX_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID | IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID         | 2       | cs.website_id                      |   11 |                                              |
|    1 | PRIMARY     | sup_a                           | eq_ref | PRIMARY,UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID                                             | PRIMARY                                                        | 4       | sup_ap.product_super_attribute_id  |    1 |                                              |
|    1 | PRIMARY     | mt                              | ref    | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                     | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID                  | 6       | sup_a.product_id,const             |    1 |                                              |
|    1 | PRIMARY     | catalog_product_super_attribute | ref    | UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID                                                     | UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID    | 4       | sup_a.product_id                   |    1 | Using index; FirstMatch(mt)                  |
|    1 | PRIMARY     | supl                            | ref    | UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID                         | IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID                       | 4       | sup_a.product_id                   |    4 |                                              |
|    1 | PRIMARY     | pint                            | ref    | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID            | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 6       | supl.product_id,sup_a.attribute_id |    1 | Using where                                  |
+------+-------------+---------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+

I have no experience about optimize the select query with stuck at sending data, I tried update the select query to this: 

SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4),
          mt.value + SUM(sup_ap.pricing_value)) AS `value`,
       75                                       AS `attribute_id`,
       `supl`.`product_id`                      AS `entity_id`,
       `cs`.`store_id`
FROM (select entity_id, `value` from `catalog_product_entity_decimal` where attribute_id = '75') AS `mt`
         LEFT JOIN `catalog_product_super_attribute` AS `sup_a` ON mt.entity_id = product_id
         INNER JOIN `catalog_product_super_attribute_pricing` AS `sup_ap`
                    ON sup_ap.product_super_attribute_id = sup_a.product_super_attribute_id
         INNER JOIN `catalog_product_super_link` AS `supl` ON mt.entity_id = supl.parent_id
         INNER JOIN `catalog_product_entity_int` AS `pint`
                    ON pint.entity_id = supl.product_id and pint.attribute_id = sup_a.attribute_id and
                       pint.value = sup_ap.value_index
         INNER JOIN `core_store` AS `cs` ON cs.website_id = sup_ap.website_id
WHERE (sup_a.product_id is not null)
GROUP BY `entity_id`, `cs`.`store_id`
LIMIT 500;

New Explain: 

+------+-------------+--------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| id   | select_type | table                          | type   | possible_keys                                                                                                                                                  | key                                                            | key_len | ref                                | rows | Extra                                        |
+------+-------------+--------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | cs                             | index  | IDX_CORE_STORE_WEBSITE_ID                                                                                                                                      | IDX_CORE_STORE_WEBSITE_ID                                      | 2       | NULL                               |    7 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | sup_ap                         | ref    | UNQ_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID_VAL_IDX_WS_ID,IDX_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID | IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID         | 2       | cs.website_id                      |   11 |                                              |
|    1 | SIMPLE      | sup_a                          | eq_ref | PRIMARY,UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID                                             | PRIMARY                                                        | 4       | sup_ap.product_super_attribute_id  |    1 | Using where                                  |
|    1 | SIMPLE      | catalog_product_entity_decimal | ref    | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                     | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID                  | 6       | sup_a.product_id,const             |    1 |                                              |
|    1 | SIMPLE      | supl                           | ref    | UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID                         | IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID                       | 4       | sup_a.product_id                   |    4 |                                              |
|    1 | SIMPLE      | pint                           | ref    | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID            | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 6       | supl.product_id,sup_a.attribute_id |    1 | Using where                                  |
+------+-------------+--------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+

Update 1: Table Structors

1: catalog_product_entity_decimal;

CREATE TABLE `catalog_product_entity_decimal` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `entity_type_id` smallint(5) unsigned NOT NULL COMMENT 'Entity Type ID',
  `attribute_id` smallint(5) unsigned NOT NULL COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
  `value` decimal(12,4) DEFAULT NULL COMMENT 'Value',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID` (`attribute_id`),
  CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_DEC_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_DEC_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=28087876 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Decimal Attribute Backend Table'

2: catalog_product_super_attribute_pricing

CREATE TABLE `catalog_product_super_attribute_pricing` (
  `value_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `product_super_attribute_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product Super Attribute ID',
  `value_index` varchar(255) NOT NULL COMMENT 'Value Index',
  `is_percent` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Percent',
  `pricing_value` decimal(12,4) DEFAULT NULL COMMENT 'Pricing Value',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website ID',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `UNQ_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID_VAL_IDX_WS_ID` (`product_super_attribute_id`,`value_index`,`website_id`),
  KEY `IDX_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID` (`product_super_attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID` (`website_id`),
  CONSTRAINT `FK_CAT_PRD_SPR_ATTR_PRICING_WS_ID_CORE_WS_WS_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CDE8813117106CFAA3AD209358F66332` FOREIGN KEY (`product_super_attribute_id`) REFERENCES `catalog_product_super_attribute` (`product_super_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Super Attribute Pricing Table'

3: catalog_product_super_link

CREATE TABLE `catalog_product_super_link` (
  `link_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Link ID',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent ID',
  PRIMARY KEY (`link_id`),
  UNIQUE KEY `UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID` (`product_id`,`parent_id`),
  KEY `IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID` (`parent_id`),
  KEY `IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID` (`product_id`),
  CONSTRAINT `FK_CAT_PRD_SPR_LNK_PARENT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`parent_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_SPR_LNK_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1200 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Super Link Table'

4: catalog_product_entity_int

CREATE TABLE `catalog_product_entity_int` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `entity_type_id` int(10) unsigned NOT NULL COMMENT 'Entity Type ID',
  `attribute_id` smallint(5) unsigned NOT NULL COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
  `value` int(11) DEFAULT NULL COMMENT 'Value',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID` (`entity_id`),
  CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_INT_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36351339 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table'

5: core_store

CREATE TABLE `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
  `code` varchar(32) DEFAULT NULL COMMENT 'Code',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `name` varchar(255) NOT NULL COMMENT 'Store Name',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
  KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
  KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
  KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`),
  CONSTRAINT `FK_CORE_STORE_GROUP_ID_CORE_STORE_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CORE_STORE_WEBSITE_ID_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Stores'

Extremely grateful that you could take a moment to consider keep an eye on the issue above.

Update 2

I find out the problem that I have a join condition with 2 two columns has difference type.

pint.value = sup_ap.value_index
Improve this question
2
  • do you use InnoDB or MyISAM? did you run analyze table command for each table in query?
    – NikitaSerbskiy
    Commented Mar 30, 2020 at 16:57
  • Hi @Nikita i'm using InnoDB, Not yet run analyze table for each table in query.
    – HoangHieu
    Commented Mar 31, 2020 at 2:43

1 Answer 1

Reset to default
1

Please check this query:

SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4),
          mt.value + SUM(sup_ap.pricing_value)) AS `value`,
       75                                       AS `attribute_id`,
       `supl`.`product_id`                      AS `entity_id`,
       `cs`.`store_id`
FROM 
(

select entity_id
from catalog_product_entity_decimal
where attribute_id = '75'
and entity_id in (select product_id from catalog_product_super_attribute)
limit 500
) AS cte

join    `catalog_product_entity_decimal` AS `mt` ON mt.entity_id = cte.entity_id
         LEFT JOIN `catalog_product_super_attribute` AS `sup_a` ON mt.entity_id = product_id
         INNER JOIN `catalog_product_super_attribute_pricing` AS `sup_ap`
                    ON sup_ap.product_super_attribute_id = sup_a.product_super_attribute_id
         INNER JOIN `catalog_product_super_link` AS `supl` ON mt.entity_id = supl.parent_id
         INNER JOIN `catalog_product_entity_int` AS `pint`
                    ON pint.entity_id = supl.product_id and pint.attribute_id = sup_a.attribute_id and
                       pint.value = sup_ap.value_index
         INNER JOIN `core_store` AS `cs` ON cs.website_id = sup_ap.website_id
GROUP BY `supl`.`product_id` , `cs`.`store_id`
Improve this answer
7
  • Hi @Nikita can you please explain your answer?
    – HoangHieu
    Commented Mar 31, 2020 at 2:44
  • @HoangHieu does modified query work better? I just tried to reduce the amount of affected rows on earlier stage to avoid grouping those ones which will be excluded by limit clause.
    – NikitaSerbskiy
    Commented Mar 31, 2020 at 7:40
  • Hi @Nikita I added table structors.
    – HoangHieu
    Commented Apr 3, 2020 at 4:37
  • @HoangHieu have you tried my query? how fast does it work?
    – NikitaSerbskiy
    Commented Apr 3, 2020 at 9:03
  • I tried, still not work, but can you please explains your opinions?
    – HoangHieu
    Commented Apr 3, 2020 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.