SELECT 
  pfv.feature_id, 
  pfv.product_id, 
  pfv.variant_id, 
  gp.group_id 
FROM 
  q_product_features_values AS pfv 
  INNER JOIN q_product_variation_group_products AS gp ON pfv.product_id = gp.product_id 
  INNER JOIN q_product_variation_group_features AS gpf ON gpf.group_id = gp.group_id 
  AND gpf.feature_id = pfv.feature_id 
WHERE 
  pfv.lang_code = 'en' 
  AND gp.group_id IN (
    36, 34, 37, 31, 39, 38, 30, 32, 33, 46, 47, 
    52, 54, 56, 55, 57
  )

Query time 0.00165

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "230.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": "4",
          "rows_examined_per_scan": 16,
          "rows_produced_per_join": 16,
          "filtered": "100.00",
          "index_condition": "(`portal`.`gpf`.`group_id` in (36,34,37,31,39,38,30,32,33,46,47,52,54,56,55,57))",
          "cost_info": {
            "read_cost": "35.21",
            "eval_cost": "3.20",
            "prefix_cost": "38.41",
            "data_read_per_join": "1K"
          },
          "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": "4",
          "ref": [
            "portal.gpf.group_id"
          ],
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 80,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "80.00",
            "eval_cost": "16.00",
            "prefix_cost": "134.41",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl"
          ],
          "key": "fpl",
          "used_key_parts": [
            "feature_id",
            "product_id",
            "lang_code"
          ],
          "key_length": "12",
          "ref": [
            "portal.gpf.feature_id",
            "portal.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 80,
          "filtered": "100.00",
          "index_condition": "((`portal`.`gpf`.`feature_id` = `portal`.`pfv`.`feature_id`) and (`portal`.`pfv`.`product_id` = `portal`.`gp`.`product_id`))",
          "cost_info": {
            "read_cost": "80.00",
            "eval_cost": "16.00",
            "prefix_cost": "230.41",
            "data_read_per_join": "61K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
73 2942 9802 30
73 3088 9801 30
73 3090 9803 30
73 3091 9804 30
74 3043 9900 31
74 3092 9865 31
74 3093 9865 31
74 3094 9865 31
74 3095 9865 31
74 3096 9865 31
74 3097 9864 31
74 3098 9864 31
74 3099 9864 31
74 3100 9864 31
74 3101 9864 31
74 3102 9900 31
74 3104 9900 31
74 3105 9900 31
74 3106 9900 31
74 3107 9902 31
74 3108 9902 31
74 3109 9902 31
74 3110 9902 31
74 3111 9902 31
73 3043 9802 31
73 3092 9801 31
73 3093 9802 31
73 3094 9803 31
73 3095 9804 31
73 3096 9805 31
73 3097 9801 31
73 3098 9802 31
73 3099 9803 31
73 3100 9804 31
73 3101 9805 31
73 3102 9801 31
73 3104 9803 31
73 3105 9804 31
73 3106 9805 31
73 3107 9801 31
73 3108 9802 31
73 3109 9803 31
73 3110 9804 31
73 3111 9805 31
73 2946 9802 32
73 3130 9803 32
73 3131 9804 32
73 3132 9805 32
73 3133 9820 32
73 2951 9802 33
73 3135 9803 33
73 3136 9804 33
73 3137 9805 33
73 3033 9801 34
73 3139 9802 34
73 3140 9803 34
73 3141 9804 34
73 3142 9805 34
73 3028 9801 36
73 3162 9802 36
73 3163 9803 36
73 3164 9804 36
73 3165 9805 36
73 3038 9801 37
73 3167 9802 37
73 3168 9803 37
73 3169 9804 37
73 3170 9805 37
73 3071 9801 38
73 3172 9802 38
73 3173 9803 38
74 3067 9866 39
74 3175 9866 39
74 3176 9866 39
74 3177 9866 39
74 3178 9902 39
74 3179 9902 39
74 3180 9902 39
74 3181 9902 39
73 3067 9801 39
73 3175 9802 39
73 3176 9803 39
73 3177 9804 39
73 3178 9801 39
73 3179 9802 39
73 3180 9803 39
73 3181 9804 39
73 2955 9802 46
73 4667 9803 46
73 4668 9804 46
73 4669 9805 46
73 2959 9802 47
73 5806 9803 47
73 5807 9804 47
73 5808 9805 47
74 2963 9898 52
74 5986 9898 52
74 5987 9898 52
74 5988 9898 52
74 5989 9900 52
74 5990 9900 52
74 5991 9900 52
74 5992 9900 52
74 5993 9900 52
73 2963 9802 52
73 5986 9803 52
73 5987 9804 52
73 5988 9805 52
73 5989 9801 52
73 5990 9802 52
73 5991 9803 52
73 5992 9804 52
73 5993 9805 52
73 2972 9802 54
73 5999 9803 54
73 6000 9804 54
73 6001 9805 54
73 2980 9802 55
73 6003 9803 55
73 6004 9804 55
73 6005 9805 55
73 2976 9802 56
73 6007 9803 56
73 6008 9804 56
73 6009 9805 56
73 2984 9802 57
73 6011 9803 57
73 6012 9804 57
73 6013 9805 57