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(
    '33426', '28850', '12449', '14280', 
    '32489'
  )

Query time 0.00087

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "37.19"
    },
    "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": 21,
          "rows_produced_per_join": 21,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "2.56",
            "eval_cost": "2.10",
            "prefix_cost": "4.66",
            "data_read_per_join": "16K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "(`dev2`.`PFV`.`product_id` in ('33426','28850','12449','14280','32489'))"
        }
      },
      {
        "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": 21,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "5.25",
            "eval_cost": "2.10",
            "prefix_cost": "12.01",
            "data_read_per_join": "158K"
          },
          "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": 16,
          "filtered": "80.46",
          "cost_info": {
            "read_cost": "5.25",
            "eval_cost": "1.69",
            "prefix_cost": "19.36",
            "data_read_per_join": "26K"
          },
          "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": 16,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "4.22",
            "eval_cost": "1.69",
            "prefix_cost": "25.27",
            "data_read_per_join": "25K"
          },
          "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.22",
            "eval_cost": "1.71",
            "prefix_cost": "31.21",
            "data_read_per_join": "46K"
          },
          "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.27",
            "eval_cost": "1.71",
            "prefix_cost": "37.19",
            "data_read_per_join": "72K"
          },
          "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
14280 Двигатель ISF3.8S5168 11785 735
14280 Мощность двигателя, л.с. 166 11786 737
14280 Колесная формула 4x2 8752 744
14280 Тип двигателя Дизельный 9392 751
28850 Двигатель WEICHAI, WP4.1NQ190E5 14060 735
28850 Мощность двигателя, л.с. 190 9363 737
28850 Грузоподъемность, кг 5800 13780 741
28850 Колесная формула 4x2 8752 744
28850 Тип двигателя Дизельный 9392 751
28850 Страна производитель Китай 9730 758
28850 Объем двигателя 4088см3 14544 838
28850 Привод Задний 13040 878
32489 Мощность двигателя, л.с. 190 9363 737
33426 Двигатель 4КН1-ТСG61 14742 735
33426 Мощность двигателя, л.с. 190 9363 737
33426 Тип двигателя Дизельный 9392 751
33426 Объем двигателя 5.2 14741 838