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 (864)

Query time 0.00148

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "60.88"
    },
    "nested_loop": [
      {
        "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": [
            "const"
          ],
          "rows_examined_per_scan": 20,
          "rows_produced_per_join": 20,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "5.00",
            "eval_cost": "2.00",
            "prefix_cost": "7.00",
            "data_read_per_join": "320"
          },
          "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": "idx_product_feature_variant_id",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "test2_gaseus.gp.product_id"
          ],
          "rows_examined_per_scan": 7,
          "rows_produced_per_join": 76,
          "filtered": "54.46",
          "using_index": true,
          "cost_info": {
            "read_cost": "13.19",
            "eval_cost": "7.63",
            "prefix_cost": "34.19",
            "data_read_per_join": "58K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ],
          "attached_condition": "(`test2_gaseus`.`pfv`.`lang_code` = 'en')"
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "group_id"
          ],
          "key_length": "6",
          "ref": [
            "test2_gaseus.pfv.feature_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 76,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "19.06",
            "eval_cost": "7.63",
            "prefix_cost": "60.88",
            "data_read_per_join": "7K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12506 1935 864
624 12506 2381 864
625 12506 2131 864
627 12506 2424 864
628 12506 1660 864
553 12505 1935 864
624 12505 2381 864
625 12505 2415 864
627 12505 2424 864
628 12505 1660 864
553 12504 1935 864
624 12504 2381 864
625 12504 2423 864
627 12504 2424 864
628 12504 1660 864
553 12514 1935 864
624 12514 2381 864
625 12514 2423 864
627 12514 1836 864
628 12514 1660 864
553 12513 1935 864
624 12513 1803 864
625 12513 2173 864
627 12513 2424 864
628 12513 1660 864
553 12512 1935 864
624 12512 1803 864
625 12512 2426 864
627 12512 2424 864
628 12512 1660 864
553 12511 1935 864
624 12511 1803 864
625 12511 2131 864
627 12511 2424 864
628 12511 1660 864
553 12510 1935 864
624 12510 1803 864
625 12510 2415 864
627 12510 2424 864
628 12510 1660 864
553 12509 1935 864
624 12509 1803 864
625 12509 2423 864
627 12509 2424 864
628 12509 1660 864
553 12508 1935 864
624 12508 2381 864
625 12508 2173 864
627 12508 2424 864
628 12508 1660 864
553 12507 1935 864
624 12507 2381 864
625 12507 2426 864
627 12507 2424 864
628 12507 1660 864
553 12516 1935 864
624 12516 2381 864
625 12516 2131 864
627 12516 1836 864
628 12516 1660 864
553 12515 1935 864
624 12515 2381 864
625 12515 2415 864
627 12515 1836 864
628 12515 1660 864
553 12517 1935 864
624 12517 2381 864
625 12517 2426 864
627 12517 1836 864
628 12517 1660 864
553 12518 1935 864
624 12518 2381 864
625 12518 2173 864
627 12518 1836 864
628 12518 1660 864
553 12519 1935 864
624 12519 1803 864
625 12519 2423 864
627 12519 1836 864
628 12519 1660 864
553 12520 1935 864
624 12520 1803 864
625 12520 2415 864
627 12520 1836 864
628 12520 1660 864
553 12522 1935 864
624 12522 1803 864
625 12522 2426 864
627 12522 1836 864
628 12522 1660 864
553 12521 1935 864
624 12521 1803 864
625 12521 2131 864
627 12521 1836 864
628 12521 1660 864
553 12523 1935 864
624 12523 1803 864
625 12523 2173 864
627 12523 1836 864
628 12523 1660 864