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(
    '7869', '7868', '9004', '11388', '9320'
  )

Query time 0.01671

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "45.07"
    },
    "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 ('7869','7868','9004','11388','9320'))"
        }
      },
      {
        "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": "2.70",
            "eval_cost": "3.06",
            "prefix_cost": "7.02",
            "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": "17.74",
            "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": "7.58",
            "eval_cost": "2.46",
            "prefix_cost": "27.78",
            "data_read_per_join": "37K"
          },
          "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": 24,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "6.16",
            "eval_cost": "2.47",
            "prefix_cost": "36.40",
            "data_read_per_join": "104K"
          },
          "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": 24,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "6.17",
            "eval_cost": "2.50",
            "prefix_cost": "45.07",
            "data_read_per_join": "68K"
          },
          "used_columns": [
            "feature_id",
            "internal_name"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
7868 Двигатель Isuzu GH-6HK1 11165 735
7868 Мощность двигателя, кВт 212 11166 736
7868 Мощность двигателя, л.с. 288 11848 737
7868 Страна производитель Китай 9730 758
7868 Объем ковша, м3 1.6 11167 796
7868 Ширина гусеницы, мм 600 11183 815
7869 Двигатель Isuzu GH-6HK1 11165 735
7869 Мощность двигателя, л.с. 288 11848 737
7869 Страна производитель Китай 9730 758
7869 Объем ковша, м3 1.9 10880 796
7869 Ширина гусеницы, мм 600 11183 815
9004 Двигатель Isuzu GH-6HK1 11165 735
9004 Мощность двигателя, л.с. 288 11848 737
9004 Объем ковша, м3 1.6 11167 796
9004 Эксплуатационная масса, кг 32800 11169 811
9320 Двигатель Isuzu GH-6HK1 11165 735
9320 Мощность двигателя, кВт 212 11166 736
9320 Мощность двигателя, л.с. 288.3 12337 737
9320 Габариты техники д.ш.в., мм 11395x3190x3900 12338 738
9320 Страна производитель Китай 9730 758
9320 Объем ковша, м3 2 10832 796
9320 Глубина копания, мм 7020 12339 797
9320 Эксплуатационная масса, кг 39300 11941 811
9320 Ширина гусеницы, мм 690 12341 815
9320 Длина гусеницы, мм 4140 12340 821
11388 Объем ковша, м3 2 10832 796