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 = 'en' 
  AND gp.group_id IN (
    143, 141, 140, 139, 78, 77, 76, 75, 74, 
    73, 68, 67, 65, 64
  )

Query time 0.00089

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "63.31"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "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": 52,
          "rows_produced_per_join": 52,
          "filtered": "100.00",
          "index_condition": "(`wearethehelpful_scalesta_net`.`gp`.`group_id` in (143,141,140,139,78,77,76,75,74,73,68,67,65,64))",
          "cost_info": {
            "read_cost": "21.71",
            "eval_cost": "5.20",
            "prefix_cost": "26.91",
            "data_read_per_join": "832"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "wearethehelpful_scalesta_net.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 52,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "13.00",
            "eval_cost": "5.20",
            "prefix_cost": "45.11",
            "data_read_per_join": "5K"
          },
          "used_columns": [
            "feature_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": [
            "wearethehelpful_scalesta_net.gpf.feature_id",
            "wearethehelpful_scalesta_net.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 52,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "13.00",
            "eval_cost": "5.20",
            "prefix_cost": "63.31",
            "data_read_per_join": "40K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
582 1145 1309 64
582 1146 1310 64
582 1147 1311 64
582 1148 1312 64
585 1154 1324 65
574 1154 1320 65
585 1159 1323 65
574 1159 1322 65
585 1158 1325 65
574 1158 1321 65
585 1161 1325 65
574 1161 1322 65
585 1160 1324 65
574 1160 1322 65
585 1153 1325 65
574 1153 1296 65
585 1152 1324 65
574 1152 1296 65
585 1151 1323 65
574 1151 1296 65
585 1150 1323 65
574 1150 1320 65
585 1155 1325 65
574 1155 1320 65
585 1156 1323 65
574 1156 1321 65
585 1157 1324 65
574 1157 1321 65
585 1226 1323 67
585 1227 1324 67
585 1228 1325 67
585 1229 1323 68
574 1229 1320 68
585 1230 1323 68
574 1230 1296 68
585 1231 1324 68
574 1231 1296 68
585 1232 1325 68
574 1232 1296 68
585 1233 1324 68
574 1233 1320 68
585 1234 1325 68
574 1234 1320 68
585 1235 1323 68
574 1235 1321 68
585 1236 1324 68
574 1236 1321 68
585 1237 1325 68
574 1237 1321 68
585 1238 1323 68
574 1238 1322 68
585 1239 1324 68
574 1239 1322 68
585 1240 1325 68
574 1240 1322 68
555 1258 1245 73
555 1259 1283 73
555 1260 1343 73
555 1261 1245 74
555 1262 1283 74
555 1263 1343 74
555 1264 1245 75
555 1265 1283 75
555 1266 1343 75
555 1267 1344 76
555 1268 1245 76
555 1269 1283 76
555 1270 1343 76
555 1271 1245 77
555 1272 1283 77
555 1273 1343 77
555 1274 1245 78
555 1275 1283 78
555 1276 1343 78
568 2053 1415 140
568 2054 1418 140
568 2015 1463 140
568 2016 1279 140
568 2017 1279 141
568 2018 1421 141
568 2052 1421 143
568 2021 1279 143
568 2022 1415 143