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('14714', '9184', '28163')

Query time 0.00075

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.79"
    },
    "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": 3,
          "rows_produced_per_join": 3,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "0.56",
            "eval_cost": "0.30",
            "prefix_cost": "0.86",
            "data_read_per_join": "22K"
          },
          "used_columns": [
            "product_id"
          ],
          "attached_condition": "(`dev2`.`P`.`product_id` in ('14714','9184','28163'))"
        }
      },
      {
        "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": 18,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.07",
            "eval_cost": "1.84",
            "prefix_cost": "3.77",
            "data_read_per_join": "14K"
          },
          "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": 14,
          "filtered": "80.46",
          "cost_info": {
            "read_cost": "4.59",
            "eval_cost": "1.48",
            "prefix_cost": "10.20",
            "data_read_per_join": "23K"
          },
          "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": 14,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "3.69",
            "eval_cost": "1.48",
            "prefix_cost": "15.37",
            "data_read_per_join": "22K"
          },
          "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": 14,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "3.69",
            "eval_cost": "1.49",
            "prefix_cost": "20.56",
            "data_read_per_join": "41K"
          },
          "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": 14,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "3.74",
            "eval_cost": "1.50",
            "prefix_cost": "25.79",
            "data_read_per_join": "63K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
9184 Мощность двигателя, л.с. 285 12072 737
9184 Колесная формула 6x6 8735 744
9184 Шины 425/85 R21 9218 745
9184 Объем топливного бака, л. 210 12073 746
9184 Количество мест в автобусе 28+2 11719 750
14714 Мощность двигателя, л.с. 273 8764 737
14714 Колесная формула 6х6 13865 744
14714 Объем топливного бака, л. 300 9494 746
14714 Количество мест в автобусе 28+2 11719 750
28163 Мощность двигателя, л.с. 300 8737 737
28163 Колесная формула 6x6 8735 744
28163 Шины 10 R22.5 9276 745
28163 Объем топливного бака, л. 210 12073 746
28163 Максимальная скорость, км/ч 160 13871 748
28163 Количество мест в автобусе 28+2 11719 750