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 = 'ru' 
  AND gp.group_id IN (97, 52, 102, 103, 107, 105, 104)

Query time 0.00050

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "60.21"
    },
    "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": 14,
          "rows_produced_per_join": 14,
          "filtered": "100.00",
          "index_condition": "(`cscart`.`gp`.`group_id` in (97,52,102,103,107,105,104))",
          "cost_info": {
            "read_cost": "23.81",
            "eval_cost": "2.80",
            "prefix_cost": "26.61",
            "data_read_per_join": "224"
          },
          "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": [
            "cscart.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 14,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "14.00",
            "eval_cost": "2.80",
            "prefix_cost": "43.41",
            "data_read_per_join": "1K"
          },
          "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": [
            "cscart.gpf.feature_id",
            "cscart.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 14,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "14.00",
            "eval_cost": "2.80",
            "prefix_cost": "60.21",
            "data_read_per_join": "10K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
549 675 1255 52
549 676 1226 52
549 677 1201 52
549 891 1224 97
549 890 1226 97
549 912 1224 102
549 911 1226 102
549 917 1224 103
549 916 1308 103
549 915 1307 103
549 914 1226 103
549 919 1223 104
549 918 1226 104
549 921 1224 105
549 920 1226 105
549 925 1224 107
549 924 1226 107