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', '28610', '28612', '28608', 
    '27289'
  )

Query time 0.00122

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "42.95"
    },
    "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','28610','28612','28608','27289'))"
        }
      },
      {
        "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.92",
            "eval_cost": "3.06",
            "prefix_cost": "6.24",
            "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.95",
            "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.57",
            "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.22",
            "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.95",
            "data_read_per_join": "105K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
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
28610 Двигатель Weichai WP4 14292 735
28610 Мощность двигателя, л.с. 185 9338 737
28610 Габариты техники д.ш.в., мм 9900 x 2550 x 3300 14291 738
28610 Вылет стрелы, мм 7900 14290 743
28610 Колесная формула 4x2 8752 744
28610 Экологический стандарт Евро-5 9138 749
28610 Страна производитель Китай 9730 758
28612 Двигатель Weichai 12754 735
28612 Мощность двигателя, л.с. 185 9338 737
28612 Габариты техники д.ш.в., мм 9900 x 2550 x 3400 14295 738
28612 Вылет стрелы, мм 11000 10521 743
28612 Колесная формула 4x2 8752 744
28612 Экологический стандарт Евро-5 9138 749
28612 Страна производитель Китай 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