SELECT 
  COUNT(DISTINCT pf.feature_id) 
FROM 
  cscart_product_features AS pf 
  LEFT JOIN cscart_product_features AS pf_groups ON pf.parent_id = pf_groups.feature_id 
  LEFT JOIN cscart_product_features_descriptions AS pf_groups_description ON pf_groups_description.feature_id = pf.parent_id 
  AND pf_groups_description.lang_code = 'en' 
  LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id 
  AND cscart_product_features_descriptions.lang_code = 'en' 
WHERE 
  1 
  AND pf.feature_type != 'G' 
  AND pf.status IN ('H') 
  AND (
    pf_groups.status IN ('H') 
    OR pf_groups.status IS NULL
  ) 
  AND pf.parent_id = 651

Query time 0.00061

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "pf",
          "access_type": "ref",
          "possible_keys": ["status"],
          "key": "status",
          "key_length": "3",
          "used_key_parts": ["status"],
          "ref": ["const"],
          "rows": 16,
          "filtered": 100,
          "index_condition": "pf.`status` = 'H'",
          "attached_condition": "pf.parent_id = 651 and pf.feature_type <> 'G'"
        }
      },
      {
        "table": {
          "table_name": "pf_groups",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["feature_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(pf_groups.`status` = 'H' or pf_groups.`status` is null)"
        }
      }
    ]
  }
}