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(
    '30275', '30271', '28970', '28961', 
    '28942'
  )

Query time 0.00124

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 ('30275','30271','28970','28961','28942'))"
        }
      },
      {
        "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
28942 Двигатель Evotech A274 14412 735
28942 Мощность двигателя, л.с. 106.8 14394 737
28942 Грузоподъемность, кг 1500 14416 741
28942 Колесная формула 4x2 8752 744
28942 Тип двигателя Газобензиновый 11061 751
28942 Страна производитель Россия 9729 758
28942 Объем двигателя 2.7 14350 838
28942 Привод Задний 13040 878
28961 Двигатель Бензин/газовый (пропан) 14391 735
28961 Мощность двигателя, л.с. 106.8 14394 737
28961 Грузоподъемность, кг 1500 9410 741
28961 Колесная формула 4x2 8752 744
28961 Тип двигателя Газобензиновый 11061 751
28961 Страна производитель Россия 9729 758
28961 Объем двигателя 2.7 14350 838
28961 Привод Задний 13040 878
28970 Двигатель Evotech A274 14412 735
28970 Мощность двигателя, л.с. 106.8 14394 737
28970 Грузоподъемность, кг 1500 14416 741
28970 Колесная формула 4x2 8752 744
28970 Тип двигателя Газобензиновый 11061 751
28970 Страна производитель Россия 9729 758
28970 Объем двигателя 2.7 14350 838
28970 Привод Задний 13040 878
30271 Двигатель Evotech A275 14426 735
30271 Мощность двигателя, л.с. 106.8 14394 737
30271 Грузоподъемность, кг 1500 14416 741
30271 Колесная формула 4x2 8752 744
30271 Тип двигателя Газобензиновый 11061 751
30271 Страна производитель Россия 9729 758
30271 Объем двигателя 2.7 14350 838
30271 Привод Задний 13040 878
30275 Двигатель Evotech A274 14412 735
30275 Мощность двигателя, л.с. 106.8 14394 737
30275 Грузоподъемность, кг 1000 11163 741
30275 Колесная формула 4x2 8752 744
30275 Тип двигателя Бензиновый 11062 751
30275 Страна производитель Россия 9729 758
30275 Объем двигателя 2.7 14350 838
30275 Привод Задний 13040 878