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 (935, 870)

Query time 0.00202

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "69.67"
    },
    "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": 22,
          "rows_produced_per_join": 22,
          "filtered": "100.00",
          "index_condition": "(`test2_gaseus`.`gp`.`group_id` in (935,870))",
          "cost_info": {
            "read_cost": "8.21",
            "eval_cost": "2.20",
            "prefix_cost": "10.41",
            "data_read_per_join": "352"
          },
          "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": 83,
          "filtered": "54.46",
          "using_index": true,
          "cost_info": {
            "read_cost": "14.51",
            "eval_cost": "8.39",
            "prefix_cost": "40.32",
            "data_read_per_join": "64K"
          },
          "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",
            "test2_gaseus.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 83,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "20.97",
            "eval_cost": "8.39",
            "prefix_cost": "69.67",
            "data_read_per_join": "8K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12617 1935 870
624 12617 2403 870
625 12617 2436 870
627 12617 2290 870
628 12617 1712 870
553 12618 1935 870
624 12618 2403 870
625 12618 2388 870
627 12618 2290 870
628 12618 1712 870
553 12619 1935 870
624 12619 2403 870
625 12619 2433 870
627 12619 1954 870
628 12619 1712 870
553 12620 1935 870
624 12620 2403 870
625 12620 2434 870
627 12620 1954 870
628 12620 1712 870
553 12621 1935 870
624 12621 2403 870
625 12621 2435 870
627 12621 1954 870
628 12621 1712 870
553 12622 1935 870
624 12622 2403 870
625 12622 2436 870
627 12622 1954 870
628 12622 1712 870
553 12623 1935 870
624 12623 2403 870
625 12623 2388 870
627 12623 1954 870
628 12623 1712 870
553 12616 1935 870
624 12616 2403 870
625 12616 2435 870
627 12616 2290 870
628 12616 1712 870
553 12615 1935 870
624 12615 2403 870
625 12615 2434 870
627 12615 2290 870
628 12615 1712 870
553 12614 1935 870
624 12614 2403 870
625 12614 2433 870
627 12614 2290 870
628 12614 1712 870
553 12613 1935 870
624 12613 2403 870
625 12613 2388 870
627 12613 1836 870
628 12613 1712 870
553 12612 1935 870
624 12612 2403 870
625 12612 2436 870
627 12612 1836 870
628 12612 1712 870
553 12611 1935 870
624 12611 2403 870
625 12611 2435 870
627 12611 1836 870
628 12611 1712 870
553 12610 1935 870
624 12610 2403 870
625 12610 2434 870
627 12610 1836 870
628 12610 1712 870
553 12609 1935 870
624 12609 2403 870
625 12609 2433 870
627 12609 1836 870
628 12609 1712 870
553 13177 1973 935
595 13177 1905 935
622 13177 2497 935
623 13177 2501 935
624 13177 1803 935
625 13177 2123 935
626 13177 1721 935
627 13177 1659 935
628 13177 1660 935
636 13177 2348 935
637 13177 2345 935
646 13177 2369 935
647 13177 2373 935
553 13178 1973 935
595 13178 1789 935
622 13178 2497 935
623 13178 2501 935
624 13178 1803 935
625 13178 2123 935
626 13178 1721 935
627 13178 1659 935
628 13178 1660 935
636 13178 2348 935
637 13178 2345 935
646 13178 2369 935
647 13178 2373 935
553 13179 1973 935
595 13179 2499 935
622 13179 2497 935
623 13179 2501 935
624 13179 1803 935
625 13179 2123 935
626 13179 1721 935
627 13179 1659 935
628 13179 1660 935
636 13179 2348 935
637 13179 2345 935
646 13179 2369 935
647 13179 2373 935
553 13180 1973 935
595 13180 1787 935
622 13180 2497 935
623 13180 2501 935
624 13180 1803 935
625 13180 2123 935
626 13180 1721 935
627 13180 1659 935
628 13180 1660 935
636 13180 2348 935
637 13180 2345 935
646 13180 2369 935
647 13180 2373 935
553 13181 1973 935
595 13181 1900 935
622 13181 2497 935
623 13181 2501 935
624 13181 1803 935
625 13181 2123 935
626 13181 1721 935
627 13181 1659 935
628 13181 1660 935
636 13181 2348 935
637 13181 2345 935
646 13181 2369 935
647 13181 2373 935
553 13182 1973 935
595 13182 1899 935
622 13182 2497 935
623 13182 2501 935
624 13182 1803 935
625 13182 2123 935
626 13182 1721 935
627 13182 1659 935
628 13182 1660 935
636 13182 2348 935
637 13182 2345 935
646 13182 2369 935
647 13182 2373 935
553 13176 1973 935
595 13176 1904 935
622 13176 2497 935
623 13176 2501 935
624 13176 1803 935
625 13176 2123 935
626 13176 1721 935
627 13176 1659 935
628 13176 1660 935
636 13176 2348 935
637 13176 2345 935
646 13176 2369 935
647 13176 2373 935