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(
    '30273', '28972', '28974', '28952', 
    '14273'
  )

Query time 0.00097

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "32.51"
    },
    "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": 17,
          "rows_produced_per_join": 17,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "2.16",
            "eval_cost": "1.70",
            "prefix_cost": "3.86",
            "data_read_per_join": "13K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "(`dev2`.`PFV`.`product_id` in ('30273','28972','28974','28952','14273'))"
        }
      },
      {
        "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": 17,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "4.25",
            "eval_cost": "1.70",
            "prefix_cost": "9.81",
            "data_read_per_join": "128K"
          },
          "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": 13,
          "filtered": "80.46",
          "cost_info": {
            "read_cost": "4.25",
            "eval_cost": "1.37",
            "prefix_cost": "15.76",
            "data_read_per_join": "21K"
          },
          "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": 13,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "5.79",
            "eval_cost": "1.37",
            "prefix_cost": "22.91",
            "data_read_per_join": "20K"
          },
          "used_columns": [
            "variant_id"
          ]
        }
      },
      {
        "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": 13,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "3.42",
            "eval_cost": "1.37",
            "prefix_cost": "27.70",
            "data_read_per_join": "58K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      },
      {
        "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": 13,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "3.42",
            "eval_cost": "1.39",
            "prefix_cost": "32.51",
            "data_read_per_join": "38K"
          },
          "used_columns": [
            "feature_id",
            "internal_name"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
28952 Двигатель Дизельный ЯМЗ 53345 14405 735
28952 Мощность двигателя, л.с. 169 10098 737
28952 Страна производитель Россия 9729 758
28952 Крутящий момент 660 14418 905
28972 Двигатель Дизельный ЯМЗ 53345 14405 735
28972 Мощность двигателя, л.с. 169 10098 737
28972 Страна производитель Россия 9729 758
28972 Крутящий момент 660 14418 905
28974 Двигатель Дизельный ЯМЗ 53345 14405 735
28974 Мощность двигателя, л.с. 169 10098 737
28974 Страна производитель Россия 9729 758
28974 Крутящий момент 660 14418 905
30273 Двигатель Дизельный ЯМЗ 53345 14405 735
30273 Мощность двигателя, л.с. 169 10098 737
30273 Страна производитель Россия 9729 758
30273 Крутящий момент 660 14418 905