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 (
    46, 119, 120, 128, 44, 39, 40, 132, 133, 
    129, 121, 127, 126, 47, 125, 123, 124, 
    122, 130
  )

Query time 0.00086

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "159.99"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "group_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 101,
          "rows_produced_per_join": 19,
          "filtered": "18.81",
          "using_index": true,
          "cost_info": {
            "read_cost": "19.39",
            "eval_cost": "3.80",
            "prefix_cost": "23.19",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`cscart`.`gpf`.`group_id` in (46,119,120,128,44,39,40,132,133,129,121,127,126,47,125,123,124,122,130))"
        }
      },
      {
        "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": 57,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "57.00",
            "eval_cost": "11.40",
            "prefix_cost": "91.59",
            "data_read_per_join": "912"
          },
          "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": 57,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "57.00",
            "eval_cost": "11.40",
            "prefix_cost": "159.99",
            "data_read_per_join": "44K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
549 613 1311 39
549 614 1281 39
549 615 1312 39
549 616 1226 39
549 617 1311 40
549 618 1281 40
549 619 1312 40
549 620 1226 40
549 632 1226 44
549 633 1305 44
549 636 1226 46
549 637 1305 46
549 638 1226 47
549 639 1305 47
549 969 1226 119
549 970 1224 119
549 972 1226 120
549 973 1299 120
549 974 1310 120
549 975 1223 120
549 980 1226 121
549 981 1280 121
549 982 1281 121
549 983 1224 121
549 984 1201 121
549 985 1226 122
549 986 1299 122
549 987 1310 122
549 988 1223 122
549 990 1226 123
549 991 1224 123
549 992 1280 124
549 993 1302 124
549 994 1306 124
549 995 1226 124
549 996 1224 124
549 997 1280 125
549 998 1302 125
549 999 1201 125
549 1000 1226 125
549 1001 1224 125
549 1002 1280 126
549 1003 1302 126
549 1004 1226 126
549 1005 1224 126
549 1006 1201 126
549 1007 1280 127
549 1008 1302 127
549 1009 1226 127
549 1010 1224 127
549 1011 1201 127
549 1013 1226 128
549 1014 1223 128
549 1016 1226 129
549 1017 1224 129
549 1020 1226 130
549 1021 1224 130
549 621 1311 132
549 1029 1292 132
549 622 1311 133
549 1030 1199 133