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(
    '32436', '32408', '32429', '14693', 
    '27392', '27394', '33016'
  )

Query time 0.00106

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "38.89"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "PFV",
          "access_type": "range",
          "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",
          "rows_examined_per_scan": 22,
          "rows_produced_per_join": 22,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "2.61",
            "eval_cost": "2.20",
            "prefix_cost": "4.81",
            "data_read_per_join": "17K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "(`dev2`.`PFV`.`product_id` in ('32436','32408','32429','14693','27392','27394','33016'))"
        }
      },
      {
        "table": {
          "table_name": "P",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "cscart_products_product_id_index"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.PFV.product_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 22,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "5.50",
            "eval_cost": "2.20",
            "prefix_cost": "12.51",
            "data_read_per_join": "165K"
          },
          "used_columns": [
            "product_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": 17,
          "filtered": "80.46",
          "cost_info": {
            "read_cost": "5.50",
            "eval_cost": "1.77",
            "prefix_cost": "20.21",
            "data_read_per_join": "27K"
          },
          "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": 17,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "4.43",
            "eval_cost": "1.77",
            "prefix_cost": "26.40",
            "data_read_per_join": "26K"
          },
          "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": 17,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "4.43",
            "eval_cost": "1.79",
            "prefix_cost": "32.62",
            "data_read_per_join": "49K"
          },
          "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": 17,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "4.48",
            "eval_cost": "1.79",
            "prefix_cost": "38.89",
            "data_read_per_join": "76K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
27392 Грузоподъемность, кг 29760 14115 741
27392 Шины 385/65R 22.5 14108 745
27392 Страна производитель Россия 9729 758
27392 Высота ССУ, мм 1150 9822 776
27392 Объём полуприцепа, м3 85 14114 807
27394 Габариты техники д.ш.в., мм 16 990 х 2 600 х 4 000 14116 738
27394 Грузоподъемность, кг 36100 14121 741
27394 Шины 385/65R 22.5 14108 745
27394 Страна производитель Россия 9729 758
27394 Высота ССУ, мм 1150 9822 776
27394 Объём полуприцепа, м3 104 14118 807
32436 Грузоподъемность, кг 30000 8666 741
32436 Страна производитель Германия 12063 758
32436 Высота ССУ, мм 1150 9822 776
32436 Объём полуприцепа, м3 92 14340 807
33016 Грузоподъемность, кг 31120 14609 741
33016 Страна производитель Беларусь 9732 758
33016 Высота ССУ, мм 1150 9822 776
33016 Объём полуприцепа, м3 85 14114 807