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(
    '28850', '28843', '28836', '28629', 
    '8855'
  )

Query time 0.00119

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "43.86"
    },
    "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 ('28850','28843','28836','28629','8855'))"
        }
      },
      {
        "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.83",
            "eval_cost": "3.06",
            "prefix_cost": "7.16",
            "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.87",
            "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": "26.49",
            "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": "35.14",
            "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": "43.86",
            "data_read_per_join": "105K"
          },
          "used_columns": [
            "variant_id",
            "variant"
          ]
        }
      }
    ]
  }
}

Result

product_id name variant variant_id feature_id
8855 Грузоподъемность, кг 5000 9399 741
28629 Двигатель Weichai 12754 735
28629 Мощность двигателя, л.с. 185 9338 737
28629 Грузоподъемность, кг 3800 14311 741
28629 Колесная формула 4x2 8752 744
28629 Тип двигателя Дизельный 9392 751
28629 Страна производитель Китай 9730 758
28629 Объем двигателя 4000 14310 838
28629 Привод Задний 13040 878
28836 Двигатель WEICHAI, WP4.1NQ190E5 14060 735
28836 Мощность двигателя, л.с. 190 9363 737
28836 Грузоподъемность, кг 5800 13780 741
28836 Колесная формула 4х2 14489 744
28836 Тип двигателя Дизельный 9392 751
28836 Страна производитель Китай/Россия 9733 758
28836 Объем двигателя 4088см3 14544 838
28836 Привод Задний 13040 878
28843 Двигатель Weichai WP3NQ150E50 14535 735
28843 Мощность двигателя, л.с. 150 9659 737
28843 Грузоподъемность, кг 2000 11021 741
28843 Колесная формула 4x2 8752 744
28843 Тип двигателя Дизельный 9392 751
28843 Страна производитель Китай 9730 758
28843 Объем двигателя 2970см2 14534 838
28843 Привод Задний 13040 878
28850 Двигатель WEICHAI, WP4.1NQ190E5 14060 735
28850 Мощность двигателя, л.с. 190 9363 737
28850 Грузоподъемность, кг 5800 13780 741
28850 Колесная формула 4x2 8752 744
28850 Тип двигателя Дизельный 9392 751
28850 Страна производитель Китай 9730 758
28850 Объем двигателя 4088см3 14544 838
28850 Привод Задний 13040 878