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(
    '33018', '28608', '27289', '14655', 
    '14430'
  )

Query time 0.00100

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 ('33018','28608','27289','14655','14430'))"
        }
      },
      {
        "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
14430 Грузоподъемность, кг 5800 13780 741
14430 Вылет стрелы, мм 10017 13779 743
14430 Колесная формула 4x2 8752 744
14430 Максимальная высота подъема,м 12.3 13778 833
14655 Мощность двигателя, л.с. 107 13889 737
14655 Габариты техники д.ш.в., мм 4000х2100х400 13887 738
14655 Грузоподъемность, кг 990 13888 741
14655 Колесная формула 4x2 8752 744
14655 Страна производитель Россия 9729 758
14655 Максимальная высота подъема,м 4.66 13886 833
27289 Двигатель WEICHAI, WP4.1NQ190E5 14060 735
27289 Мощность двигателя, л.с. 190 9363 737
27289 Грузоподъемность, кг 5000 9399 741
27289 Вылет стрелы, мм 11200 14059 743
27289 Колесная формула 4x2 8752 744
27289 Шины 8.25R20 14061 745
27289 Экологический стандарт Евро-5 9138 749
27289 Страна производитель Китай 9730 758
27289 Максимальная высота подъема,м 12.5 14058 833
28608 Двигатель Weichai 12754 735
28608 Мощность двигателя, л.с. 185 9338 737
28608 Габариты техники д.ш.в., мм 99500 x 2550 x3500 14294 738
28608 Вылет стрелы, мм 10700 14293 743
28608 Колесная формула 4x2 8752 744
28608 Экологический стандарт Евро-5 9138 749
28608 Страна производитель Китай 9730 758
33018 Двигатель ISUZU 4HK1-TCG61 13099 735
33018 Мощность двигателя, л.с. 190 9363 737
33018 Габариты техники д.ш.в., мм 8900 × 2550 × 3450 14611 738
33018 Грузоподъемность, кг 5000 9399 741
33018 Вылет стрелы, мм 14000 10491 743
33018 Колесная формула 4x2 8752 744
33018 Шины 8.25R20 14061 745
33018 Объем кузова, м3 8.5 14612 747
33018 Экологический стандарт Евро 6 11674 749
33018 Страна производитель Китай 9730 758
33018 Максимальная высота подъема,м 15 14610 833