SELECT 
  pfv.feature_id, 
  pfv.product_id, 
  pfv.variant_id, 
  gp.group_id 
FROM 
  cscart_product_features_values AS pfv 
  INNER JOIN cscart_product_variation_group_products AS gp ON pfv.product_id = gp.product_id 
  INNER JOIN cscart_product_variation_group_features AS gpf ON gpf.group_id = gp.group_id 
  AND gpf.feature_id = pfv.feature_id 
WHERE 
  pfv.lang_code = 'ru' 
  AND gp.group_id IN (57, 58, 55, 56)

Query time 0.00073

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "38.41"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 4,
          "filtered": "100.00",
          "index_condition": "(`cscart`.`gpf`.`group_id` in (57,58,55,56))",
          "cost_info": {
            "read_cost": "8.81",
            "eval_cost": "0.80",
            "prefix_cost": "9.61",
            "data_read_per_join": "416"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "gp",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "cscart.gpf.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 12,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "12.00",
            "eval_cost": "2.40",
            "prefix_cost": "24.01",
            "data_read_per_join": "192"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "fpl",
          "used_key_parts": [
            "feature_id",
            "product_id",
            "lang_code"
          ],
          "key_length": "12",
          "ref": [
            "cscart.gpf.feature_id",
            "cscart.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 12,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "12.00",
            "eval_cost": "2.40",
            "prefix_cost": "38.41",
            "data_read_per_join": "9K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
549 704 1282 55
549 703 1284 55
549 663 1226 55
549 710 1289 55
549 709 1288 55
549 708 1287 55
549 707 1286 55
549 706 1285 55
549 719 1290 56
549 718 1289 56
549 717 1288 56
549 716 1287 56
549 715 1286 56
549 714 1285 56
549 713 1284 56
549 711 1282 56
549 666 1226 56
549 724 1284 57
549 723 1288 57
549 722 1287 57
549 721 1289 57
549 720 1282 57
549 726 1293 58
549 725 1292 58
549 727 1223 58