SELECT 
  c.parent_id, 
  sm.search_phrases, 
  c.category_id 
FROM 
  cscart_categories AS c 
  LEFT JOIN cscart_ab__search_motivation AS sm ON sm.category_id = c.category_id 
  AND sm.lang_code = 'en' 
WHERE 
  c.id_path LIKE "%306%" 
  AND (
    sm.search_phrases != "NULL" 
    OR c.level = (
      SELECT 
        level 
      FROM 
        cscart_categories 
      WHERE 
        category_id = 306
    )
  ) 
  AND c.company_id = 0 
  AND c.status = "A"

Query time 0.00027

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "c",
          "access_type": "ALL",
          "rows": 11,
          "filtered": 100,
          "attached_condition": "c.company_id = 0 and c.id_path like '%306%' and c.`status` = 'A'"
        }
      },
      {
        "table": {
          "table_name": "sm",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["demov2026.c.category_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(sm.search_phrases <> 'NULL' or c.`level` = (subquery#2)) and trigcond(sm.lang_code = 'en')"
        }
      }
    ],
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "nested_loop": [
            {
              "table": {
                "table_name": "cscart_categories",
                "access_type": "const",
                "possible_keys": ["PRIMARY", "p_category_id"],
                "key": "PRIMARY",
                "key_length": "3",
                "used_key_parts": ["category_id"],
                "ref": ["const"],
                "rows": 1,
                "filtered": 100
              }
            }
          ]
        }
      }
    ]
  }
}