SELECT 
  `P`.`product_id`, 
  `PFD`.`internal_name` AS `name`, 
  `PFVD`.`variant`, 
  `PFVD`.`variant_id`, 
  `PFD`.`feature_id` 
FROM 
  `cscart_product_features_values` AS `PFV` 
  JOIN `cscart_product_features` AS `PF` ON `PFV`.`feature_id` = `PF`.`feature_id` 
  JOIN `cscart_products` AS `P` ON `P`.`product_id` = `PFV`.`product_id` 
  JOIN `cscart_product_features_descriptions` AS `PFD` ON `PFV`.`feature_id` = `PFD`.`feature_id` 
  JOIN `cscart_product_feature_variants` AS `PFVS` ON `PFVS`.`variant_id` = `PFV`.`variant_id` 
  JOIN `cscart_product_feature_variant_descriptions` AS `PFVD` ON `PFVS`.`variant_id` = `PFVD`.`variant_id` 
WHERE 
  1 
  AND `PF`.`status` = 'A' 
  AND `PFV`.`product_id` IN(
    '28843', '29136', '28966', '28954', 
    '12449'
  )

Query time 0.00093

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "42.82"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "P",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "cscart_products_product_id_index"
          ],
          "key": "cscart_products_product_id_index",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "0.76",
            "eval_cost": "0.50",
            "prefix_cost": "1.26",
            "data_read_per_join": "37K"
          },
          "used_columns": [
            "product_id"
          ],
          "attached_condition": "(`dev2`.`P`.`product_id` in ('28843','29136','28966','28954','12449'))"
        }
      },
      {
        "table": {
          "table_name": "PFV",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "variant_id",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "product_id",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.P.product_id"
          ],
          "rows_examined_per_scan": 6,
          "rows_produced_per_join": 30,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.79",
            "eval_cost": "3.06",
            "prefix_cost": "6.11",
            "data_read_per_join": "23K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "PF",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "status"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.PFV.feature_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 24,
          "filtered": "80.46",
          "cost_info": {
            "read_cost": "7.65",
            "eval_cost": "2.46",
            "prefix_cost": "16.82",
            "data_read_per_join": "38K"
          },
          "used_columns": [
            "feature_id",
            "status"
          ],
          "attached_condition": "(`dev2`.`PF`.`status` = 'A')"
        }
      },
      {
        "table": {
          "table_name": "PFVS",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.PFV.variant_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "6.16",
            "eval_cost": "2.46",
            "prefix_cost": "25.44",
            "data_read_per_join": "37K"
          },
          "used_columns": [
            "variant_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "PFD",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.PFV.feature_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "6.16",
            "eval_cost": "2.49",
            "prefix_cost": "34.09",
            "data_read_per_join": "68K"
          },
          "used_columns": [
            "feature_id",
            "internal_name"
          ]
        }
      },
      {
        "table": {
          "table_name": "PFVD",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.PFV.variant_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "6.23",
            "eval_cost": "2.50",
            "prefix_cost": "42.82",
            "data_read_per_join": "105K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
12449 Мощность двигателя, л.с. 106 12492 737
12449 Колесная формула 4x2 8752 744
12449 Страна производитель Россия 9729 758
12449 Привод Задний 13040 878
28843 Двигатель Weichai WP3NQ150E50 14535 735
28843 Мощность двигателя, л.с. 150 9659 737
28843 Грузоподъемность, кг 2000 11021 741
28843 Колесная формула 4x2 8752 744
28843 Тип двигателя Дизельный 9392 751
28843 Страна производитель Китай 9730 758
28843 Объем двигателя 2970см2 14534 838
28843 Привод Задний 13040 878
28954 Двигатель GAZ G31C 14333 735
28954 Мощность двигателя, л.с. 150 9659 737
28954 Грузоподъемность, кг 1500 9410 741
28954 Колесная формула 4x2 8752 744
28954 Тип двигателя Дизельный 9392 751
28954 Страна производитель Россия 9729 758
28954 Объем двигателя 2.5 14283 838
28954 Привод Задний 13040 878
28966 Двигатель GAZ G31C 14333 735
28966 Мощность двигателя, л.с. 150 9659 737
28966 Грузоподъемность, кг 1500 14416 741
28966 Колесная формула 4x2 8752 744
28966 Тип двигателя Дизельный 9392 751
28966 Страна производитель Россия 9729 758
28966 Объем двигателя 2.5 14283 838
28966 Привод Задний 13040 878
29136 Двигатель GAZ G31C 14333 735
29136 Мощность двигателя, л.с. 150 9659 737
29136 Грузоподъемность, кг 900 12215 741
29136 Колесная формула 4x2 8752 744
29136 Тип двигателя Дизельный 9392 751
29136 Страна производитель Россия 9729 758
29136 Объем двигателя 2.5 14283 838
29136 Привод Задний 13040 878