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

Query time 0.00885

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.028190165,
    "nested_loop": [
      {
        "table": {
          "table_name": "sm",
          "access_type": "system",
          "possible_keys": ["PRIMARY"],
          "rows": 0,
          "filtered": 0,
          "const_row_not_found": true
        }
      },
      {
        "table": {
          "table_name": "c",
          "access_type": "ALL",
          "loops": 1,
          "rows": 113,
          "cost": 0.028190165,
          "filtered": 100,
          "attached_condition": "c.company_id = 0 and c.`level` = (subquery#2) and c.id_path like '%167%' and c.`status` = 'A'"
        }
      }
    ],
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "nested_loop": [
            {
              "table": {
                "table_name": "tmall_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
              }
            }
          ]
        }
      }
    ]
  }
}