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 = 'vi' 
  AND gp.group_id IN (
    13047, 13052, 13051, 13046, 13049, 13053, 
    13050, 13048
  )

Query time 0.00470

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "access_type": "range",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "rows": 18,
          "filtered": 100,
          "index_condition": "gp.group_id in (13047,13052,13051,13046,13049,13053,13050,13048)"
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "ref": ["dev_db.gp.group_id"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "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",
          "key_length": "12",
          "used_key_parts": ["product_id", "feature_id", "lang_code"],
          "ref": ["dev_db.gp.product_id", "dev_db.gpf.feature_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "pfv.lang_code = 'vi'",
          "using_index": true
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
20613 19301 72086 13046
20613 19300 72087 13046
20613 3502 72085 13046
20614 19304 72090 13047
20614 19303 72089 13047
20614 19302 72091 13047
20614 3501 72088 13047
20615 19305 72093 13048
20615 3500 72092 13048
20616 19306 72095 13049
20616 3499 72094 13049
20617 19307 72097 13050
20617 3498 72096 13050
20618 19308 72099 13051
20618 3497 72098 13051
20619 19309 72101 13052
20619 3496 72100 13052
20620 19310 72102 13053
20620 3494 72103 13053