SELECT 
  a.category_id 
FROM 
  cscart_categories as a 
  LEFT JOIN cscart_categories as b ON b.category_id IN (7822) 
WHERE 
  a.id_path LIKE CONCAT(b.id_path, '/%')

Query time 0.00218

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1494.60"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "b",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY",
            "p_category_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "category_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.20",
            "prefix_cost": "0.00",
            "data_read_per_join": "5K"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ]
        }
      },
      {
        "table": {
          "table_name": "a",
          "access_type": "index",
          "key": "id_path",
          "used_key_parts": [
            "id_path"
          ],
          "key_length": "767",
          "rows_examined_per_scan": 6988,
          "rows_produced_per_join": 776,
          "filtered": "11.11",
          "using_index": true,
          "cost_info": {
            "read_cost": "97.00",
            "eval_cost": "155.27",
            "prefix_cost": "1494.60",
            "data_read_per_join": "4M"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ],
          "attached_condition": "(`webmarco`.`a`.`id_path` like <cache>(concat('6473/7822','/%')))"
        }
      }
    ]
  }
}

Result

category_id
7918
7919
7971
7972
7973
7974
7975
7920
7921
8001
8002
8003
8004
8005
8006
8007
7922
7923
7924
7925
7926
7927
7928
7929
7930
7931
8023
8024
8025
8026
8027
8028
8029
8030
7932
7933
8060
8061
8062
8063
7934
7935
7936
7937
7938
7939
7940
7941
8078
8079
8080
8081
8082
8083
8084
8085
7942