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

Query time 0.00254

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('4001/4254','/%')))"
        }
      }
    ]
  }
}

Result

category_id
4268
4282
9010
4286
4431
4653
4687
4736
4778
4812
4844
4883
5861
5893
5922
5951
5968
5998
4475
4510
4561
4587
4924
4976
5015
5047
5124
5156
5183
5216
5247
4765
4808
5359
5397
5462
5524
9012
5553
5789
5597
4859
4915
4974
5022
5068
5110
5171
5206
5224
5245
5267
5285
5272
5290
5305
5326
5345
5363
5385
5307
5324
5375
5406
5430
5451
5468
5496
5503
5527
5516
5589
5688
5961
5546
6022
6054
6115
6164
6233
6309
7184
7199
7213
7270
7017
7045
7067
7103