Почему одно из двух почти идентичных представлений MySQL работает так медленно?

Я исследовал проблему, связанную с тем, что один из запросов нашего приложения выполняется очень медленно и, похоже, это связано с конкретным представлением, которое мы используем.

Вот полный запрос с опущенными выбранными столбцами (выбрано 408 столбцов)

SELECT `skus`.`yom_sku`
-- omitted columns (408)
FROM   `skus`
       LEFT JOIN `product_families_view`
              ON `product_families_view`.`product_id` = `skus`.`yom_sku`
       LEFT JOIN `product_profit_projection_view`
              ON `product_profit_projection_view`.`product_id` =
                 `skus`.`yom_sku`
       LEFT JOIN `product_media_types_view`
              ON `product_media_types_view`.`product_id` = `skus`.`yom_sku`
       LEFT JOIN `product`
              ON `product`.`yom_sku` = `skus`.`yom_sku`
       LEFT JOIN `product_listings`
              ON `product_listings`.`asin` = `skus`.`asin`
                 AND `product_listings`.`seller_sku` = `skus`.`upc`
       LEFT JOIN `analyst`
              ON `analyst`.`yom_sku` = `skus`.`yom_sku`
       LEFT JOIN `collection`
              ON `collection`.`yom_sku` = `skus`.`yom_sku`
       LEFT JOIN `supplier_pricing`
              ON `supplier_pricing`.`yom_sku` = `skus`.`yom_sku`
       LEFT JOIN `amazon_order`
              ON `amazon_order`.`yom_sku` = `skus`.`yom_sku`
       LEFT JOIN `amazon_pricing`
              ON `amazon_pricing`.`yom_sku` = `skus`.`yom_sku`
WHERE  `product`.`retired` = false
ORDER  BY `skus`.`upc` ASC
LIMIT  100  

Запрос довольно большой, но одно представление (product_media_types_view) по какой-то причине занимает значительный объем ресурсов чтения. Это особенно странно, поскольку по структуре оно почти идентично другому представлению под названием «product_families_view» и не имеет явных проблем с индексацией.

Вот два мнения для сравнения:

-- pricetooldb.product_families_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `pricetooldb`.`product_families_view` AS
select
    `pfp`.`product_id` AS `product_id`,
    `pfp`.`part_number` AS `part_number`,
    group_concat(`pf`.`family` order by `pf`.`family` ASC separator ', ') AS `families`
from
    (`pricetooldb`.`product_families` `pf`
join `pricetooldb`.`product_families_products` `pfp` on
    ((`pfp`.`family_id` = `pf`.`id`)))
group by
    `pfp`.`product_id`;

-- pricetooldb.product_media_types_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `pricetooldb`.`product_media_types_view` AS
select
    `pmt`.`product_id` AS `product_id`,
    group_concat(`mt`.`name` order by `mt`.`name` ASC separator ' ') AS `media_types`
from
    (`pricetooldb`.`media_types` `mt`
join `pricetooldb`.`product_media_types` `pmt` on
    ((`pmt`.`media_type_id` = `mt`.`id`)))
group by
    `pmt`.`product_id`;

Я запустил EXPLAIN EXTENDED для проблемного SQL-запроса, описанного выше, и обнаружил, что Product_media_types_view требует значительно больше ресурсов, чем очень похожий Product_families_view.

[
  {
    "table": {
      "table_name": "product_families_view",
      "access_type": "ref",
      "possible_keys": [
        "<auto_key0>"
      ],
      "key": "<auto_key0>",
      "used_key_parts": [
        "product_id"
      ],
      "key_length": "4",
      "ref": [
        "pricetooldb.skus.yom_sku"
      ],
      "rows_examined_per_scan": 10,
      "rows_produced_per_join": 75142,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "45073.14",
        "eval_cost": "15028.53",
        "prefix_cost": "165159.79",
        "data_read_per_join": "4M"
      },
      "used_columns": [
        "product_id",
        "part_number",
        "families"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "48405.37"
          },
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "cost_info": {
              "sort_cost": "21756.26"
            },
            "nested_loop": [
              {
                "table": {
                  "table_name": "pf",
                  "access_type": "index",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "product_families_family_unique",
                  "used_key_parts": [
                    "family"
                  ],
                  "key_length": "257",
                  "rows_examined_per_scan": 2653,
                  "rows_produced_per_join": 2653,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "11.00",
                    "eval_cost": "530.60",
                    "prefix_cost": "541.60",
                    "data_read_per_join": "683K"
                  },
                  "used_columns": [
                    "id",
                    "family"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "pfp",
                  "access_type": "ref",
                  "possible_keys": [
                    "product_entry_unique",
                    "product_families_products_family_id_foreign"
                  ],
                  "key": "product_families_products_family_id_foreign",
                  "used_key_parts": [
                    "family_id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "pricetooldb.pf.id"
                  ],
                  "rows_examined_per_scan": 8,
                  "rows_produced_per_join": 21756,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "21756.26",
                    "eval_cost": "4351.25",
                    "prefix_cost": "26649.11",
                    "data_read_per_join": "1M"
                  },
                  "used_columns": [
                    "id",
                    "product_id",
                    "family_id",
                    "part_number"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "table": {
      "table_name": "product_media_types_view",
      "access_type": "ref",
      "possible_keys": [
        "<auto_key0>"
      ],
      "key": "<auto_key0>",
      "used_key_parts": [
        "product_id"
      ],
      "key_length": "4",
      "ref": [
        "pricetooldb.skus.yom_sku"
      ],
      "rows_examined_per_scan": 10,
      "rows_produced_per_join": 1214678,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "728729.15",
        "eval_cost": "242935.67",
        "prefix_cost": "1321922.89",
        "data_read_per_join": "27M"
      },
      "used_columns": [
        "product_id",
        "media_types"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 5,
          "cost_info": {
            "query_cost": "122384.12"
          },
          "grouping_operation": {
            "using_filesort": false,
            "nested_loop": [
              {
                "table": {
                  "table_name": "p",
                  "access_type": "index",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "yom_sku"
                  ],
                  "key_length": "4",
                  "rows_examined_per_scan": 46669,
                  "rows_produced_per_join": 46669,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "867.00",
                    "eval_cost": "9333.80",
                    "prefix_cost": "10200.80",
                    "data_read_per_join": "74M"
                  },
                  "used_columns": [
                    "yom_sku"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "pmt",
                  "access_type": "ref",
                  "possible_keys": [
                    "product_media_types_product_id_media_type_id_unique"
                  ],
                  "key": "product_media_types_product_id_media_type_id_unique",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "5",
                  "ref": [
                    "pricetooldb.p.yom_sku"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 46795,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "46669.22",
                    "eval_cost": "9359.16",
                    "prefix_cost": "66229.17",
                    "data_read_per_join": "731K"
                  },
                  "used_columns": [
                    "id",
                    "product_id",
                    "media_type_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "mt",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "pricetooldb.pmt.media_type_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 46795,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "46795.79",
                    "eval_cost": "9359.16",
                    "prefix_cost": "122384.12",
                    "data_read_per_join": "731K"
                  },
                  "used_columns": [
                    "id",
                    "name"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
]

Мне не ясно, почему. Ниже я предоставлю DDL-файлы «product», «product_media_types» и «media_types» (с отредактированными нерелевантными столбцами из «product»).

-- pricetooldb.product definition

CREATE TABLE `product` (
  `yom_sku` int(11) unsigned NOT NULL,
  PRIMARY KEY (`yom_sku`),
  CONSTRAINT `yom_sku_prod_fk` FOREIGN KEY (`yom_sku`) REFERENCES `skus` (`yom_sku`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- pricetooldb.product_media_types definition

CREATE TABLE `product_media_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned DEFAULT NULL,
  `media_type_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_media_types_product_id_media_type_id_unique` (`product_id`,`media_type_id`),
  KEY `product_media_types_media_type_id_foreign` (`media_type_id`),
  CONSTRAINT `product_media_types_media_type_id_foreign` FOREIGN KEY (`media_type_id`) REFERENCES `media_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_media_types_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `product` (`yom_sku`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46247 DEFAULT CHARSET=latin1;

-- pricetooldb.media_types definition

CREATE TABLE `media_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

Пожалуйста, дайте мне знать, если у вас есть какие-либо предложения или идеи о том, почему «product_media_types_view» стоит так дорого.