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 (51, 99, 96, 101, 108, 109, 110)

Query time 0.00052

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "67.21"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "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": 7,
          "rows_produced_per_join": 7,
          "filtered": "100.00",
          "index_condition": "(`cscart`.`gpf`.`group_id` in (51,99,96,101,108,109,110))",
          "cost_info": {
            "read_cost": "15.41",
            "eval_cost": "1.40",
            "prefix_cost": "16.81",
            "data_read_per_join": "728"
          },
          "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": [
            "cscart.gpf.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 21,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "21.00",
            "eval_cost": "4.20",
            "prefix_cost": "42.01",
            "data_read_per_join": "336"
          },
          "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": [
            "cscart.gpf.feature_id",
            "cscart.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 21,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "21.00",
            "eval_cost": "4.20",
            "prefix_cost": "67.21",
            "data_read_per_join": "16K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
549 672 1255 51
549 673 1226 51
549 674 1201 51
549 883 1223 96
549 882 1226 96
549 899 1226 99
549 898 1308 99
549 897 1307 99
549 896 1224 99
549 907 1223 101
549 906 1310 101
549 905 1309 101
549 904 1307 101
549 903 1299 101
549 902 1226 101
549 931 1223 108
549 930 1310 108
549 929 1309 108
549 928 1307 108
549 927 1299 108
549 926 1226 108
549 934 1226 109
549 935 1224 109
549 937 1226 110
549 938 1299 110
549 939 1310 110
549 940 1223 110