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

Query time 0.00145

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 12653 1935 871
553 12652 1935 871
553 12651 1935 871
553 12650 1935 871
553 12649 1935 871
553 12648 1935 871
553 12647 1935 871
553 12646 1935 871
553 12645 1935 871
553 12644 1935 871
553 12643 1935 871
553 12642 1935 871
553 12641 1935 871
553 12640 1935 871
553 12639 1935 871
553 12624 1935 871
553 12625 1935 871
553 12626 1935 871
553 12627 1935 871
553 12628 1935 871
553 12629 1935 871
553 12630 1935 871
553 12631 1935 871
553 12632 1935 871
553 12633 1935 871
553 12634 1935 871
553 12635 1935 871
553 12636 1935 871
553 12637 1935 871
553 12638 1935 871
625 12653 2143 871
625 12652 2132 871
625 12651 2131 871
625 12650 2415 871
625 12649 2413 871
625 12648 2143 871
625 12647 2132 871
625 12646 2131 871
625 12645 2415 871
625 12644 2413 871
625 12643 2143 871
625 12642 2132 871
625 12641 2131 871
625 12640 2415 871
625 12639 2413 871
625 12624 2413 871
625 12625 2415 871
625 12626 2131 871
625 12627 2132 871
625 12628 2143 871
625 12629 2413 871
625 12630 2415 871
625 12631 2131 871
625 12632 2132 871
625 12633 2143 871
625 12634 2413 871
625 12635 2415 871
625 12636 2131 871
625 12637 2132 871
625 12638 2143 871
624 12653 1803 871
624 12652 1803 871
624 12651 1803 871
624 12650 1803 871
624 12649 1803 871
624 12648 1705 871
624 12647 1705 871
624 12646 1705 871
624 12645 1705 871
624 12644 1705 871
624 12643 1803 871
624 12642 1803 871
624 12641 1803 871
624 12640 1803 871
624 12639 1803 871
624 12624 1705 871
624 12625 1705 871
624 12626 1705 871
624 12627 1705 871
624 12628 1705 871
624 12629 1803 871
624 12630 1803 871
624 12631 1803 871
624 12632 1803 871
624 12633 1803 871
624 12634 1705 871
624 12635 1705 871
624 12636 1705 871
624 12637 1705 871
624 12638 1705 871
627 12653 1954 871
627 12652 1954 871
627 12651 1954 871
627 12650 1954 871
627 12649 1954 871
627 12648 1954 871
627 12647 1954 871
627 12646 1954 871
627 12645 1954 871
627 12644 1954 871
627 12643 1836 871
627 12642 1836 871
627 12641 1836 871
627 12640 1836 871
627 12639 1836 871
627 12624 2290 871
627 12625 2290 871
627 12626 2290 871
627 12627 2290 871
627 12628 2290 871
627 12629 2290 871
627 12630 2290 871
627 12631 2290 871
627 12632 2290 871
627 12633 2290 871
627 12634 1836 871
627 12635 1836 871
627 12636 1836 871
627 12637 1836 871
627 12638 1836 871
628 12653 1660 871
628 12652 1660 871
628 12651 1660 871
628 12650 1660 871
628 12649 1660 871
628 12648 1660 871
628 12647 1660 871
628 12646 1660 871
628 12645 1660 871
628 12644 1660 871
628 12643 1660 871
628 12642 1660 871
628 12641 1660 871
628 12640 1660 871
628 12639 1660 871
628 12624 1660 871
628 12625 1660 871
628 12626 1660 871
628 12627 1660 871
628 12628 1660 871
628 12629 1660 871
628 12630 1660 871
628 12631 1660 871
628 12632 1660 871
628 12633 1660 871
628 12634 1660 871
628 12635 1660 871
628 12636 1660 871
628 12637 1660 871
628 12638 1660 871