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

Query time 0.00175

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "85.40"
    },
    "nested_loop": [
      {
        "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": [
            "const"
          ],
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 4,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.40",
            "prefix_cost": "1.40",
            "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": [
            "const"
          ],
          "rows_examined_per_scan": 30,
          "rows_produced_per_join": 120,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "30.00",
            "eval_cost": "12.00",
            "prefix_cost": "43.40",
            "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",
            "idx_product_feature_variant_id"
          ],
          "key": "fpl",
          "used_key_parts": [
            "feature_id",
            "product_id",
            "lang_code"
          ],
          "key_length": "12",
          "ref": [
            "test2_gaseus.gpf.feature_id",
            "test2_gaseus.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 120,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "30.00",
            "eval_cost": "12.00",
            "prefix_cost": "85.40",
            "data_read_per_join": "92K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12725 1935 874
553 12726 1935 874
553 12727 1935 874
553 12728 1935 874
553 12729 1935 874
553 12730 1935 874
553 12731 1935 874
553 12732 1935 874
553 12733 1935 874
553 12734 1935 874
553 12735 1935 874
553 12736 1935 874
553 12737 1935 874
553 12738 1935 874
553 12739 1935 874
553 12740 1935 874
553 12741 1935 874
553 12742 1935 874
553 12743 1935 874
553 12724 1935 874
553 12723 1935 874
553 12722 1935 874
553 12721 1935 874
553 12720 1935 874
553 12719 1935 874
553 12718 1935 874
553 12717 1935 874
553 12716 1935 874
553 12715 1935 874
553 12714 1935 874
625 12725 2415 874
625 12726 2131 874
625 12727 2132 874
625 12728 2143 874
625 12729 2413 874
625 12730 2415 874
625 12731 2131 874
625 12732 2132 874
625 12733 2143 874
625 12734 2413 874
625 12735 2415 874
625 12736 2131 874
625 12737 2132 874
625 12738 2143 874
625 12739 2413 874
625 12740 2415 874
625 12741 2131 874
625 12742 2132 874
625 12743 2143 874
625 12724 2413 874
625 12723 2143 874
625 12722 2132 874
625 12721 2131 874
625 12720 2415 874
625 12719 2413 874
625 12718 2143 874
625 12717 2132 874
625 12716 2131 874
625 12715 2415 874
625 12714 2413 874
624 12725 1705 874
624 12726 1705 874
624 12727 1705 874
624 12728 1705 874
624 12729 1803 874
624 12730 1803 874
624 12731 1803 874
624 12732 1803 874
624 12733 1803 874
624 12734 1705 874
624 12735 1705 874
624 12736 1705 874
624 12737 1705 874
624 12738 1705 874
624 12739 1803 874
624 12740 1803 874
624 12741 1803 874
624 12742 1803 874
624 12743 1803 874
624 12724 1705 874
624 12723 1803 874
624 12722 1803 874
624 12721 1803 874
624 12720 1803 874
624 12719 1803 874
624 12718 1705 874
624 12717 1705 874
624 12716 1705 874
624 12715 1705 874
624 12714 1705 874
627 12725 1836 874
627 12726 1836 874
627 12727 1836 874
627 12728 1836 874
627 12729 1836 874
627 12730 1836 874
627 12731 1836 874
627 12732 1836 874
627 12733 1836 874
627 12734 1954 874
627 12735 1954 874
627 12736 1954 874
627 12737 1954 874
627 12738 1954 874
627 12739 1954 874
627 12740 1954 874
627 12741 1954 874
627 12742 1954 874
627 12743 1954 874
627 12724 1836 874
627 12723 2290 874
627 12722 2290 874
627 12721 2290 874
627 12720 2290 874
627 12719 2290 874
627 12718 2290 874
627 12717 2290 874
627 12716 2290 874
627 12715 2290 874
627 12714 2290 874
628 12725 1712 874
628 12726 1712 874
628 12727 1712 874
628 12728 1712 874
628 12729 1712 874
628 12730 1712 874
628 12731 1712 874
628 12732 1712 874
628 12733 1712 874
628 12734 1712 874
628 12735 1712 874
628 12736 1712 874
628 12737 1712 874
628 12738 1712 874
628 12739 1712 874
628 12740 1712 874
628 12741 1712 874
628 12742 1712 874
628 12743 1712 874
628 12724 1712 874
628 12723 1712 874
628 12722 1712 874
628 12721 1712 874
628 12720 1712 874
628 12719 1712 874
628 12718 1712 874
628 12717 1712 874
628 12716 1712 874
628 12715 1712 874
628 12714 1712 874