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

Query time 0.00373

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "877.48"
    },
    "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.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "2K"
          },
          "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": 7821,
          "rows_produced_per_join": 868,
          "filtered": "11.11",
          "using_index": true,
          "cost_info": {
            "read_cost": "95.38",
            "eval_cost": "86.89",
            "prefix_cost": "877.48",
            "data_read_per_join": "2M"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ],
          "attached_condition": "(`maniamall_cscart`.`a`.`id_path` like <cache>(concat('286','/%')))"
        }
      }
    ]
  }
}

Result

category_id
14399
36085
39203
40128
40198
16209
16210
29492
36150
16340
40148
40153
40167
41139
16343
40782
40783
40784
40785
40786
40787
16362
16374
16494
29493
36089
36147
36157
41434
30571
30572
30575
30965
30966
36151
36152
36154
40779
40780
40781
32211
35083
35775
35809
36059
36076
36105
40166
40177
40498
40500
40502
40503
40794
40795
40796
40797
40798
36102
36123
39202
39847
40152
40155
40193
40215
41055
41432
42575
42577
42611
42614
16293
16415
29091
30283
30391
30542
30567
40515
40516
40517
40518
40519
40520
40521
36087
40508
40509
40510
40511
40512
40513
40186
40524
40507
40514
40522
40523
28866
295
12291
12326
12347
12377
12450
12517
12526
12531
36061
36065
41415
36099
36103
36104
36125
40792
36127
40143
41069
12296
12297
12358
12365
12401
12298
12415
12495
15639
30284
42558
13054
15757
15848
30213
30218
30219
30224
30299
41140
41141
41143
30222
30223
30226
30227
30228
30229
30233
30319
41147
41148
41149
41150
41151
41152
30297
41056
41071
42568
42600
30239
30379
30250
30251
30311
30356
32182
42591
40930
41770
42586
41855
42314
42559
42567
42583
42592
42599
42602
42606
42617
42618
42555
42556
42557
42565
42580
42620
42902
42560
42561
42562
42563
42589
42598
42621
42564
42566
42569
42570
42571
42572
42573
42574
42900
42576
42578
42579
42581
42582
42585
42587
42588
42590
42593
42594
42595
42596
42597
42607
42610
42613
42619
42601
42603
42604
42605
42608
42609
42612
42615
42616
42888
42889
42890
42898
42892
42896
42897
42901
42903
42905
42906
42907
42911
42912
30717
30718
35375
40156
35668
36058
36066
36067
36068
36106
38229
39209
40154
40157
40497
40527
40528
40529
40531
40532
40533
40534
40535
40536
36062
36063
38196
40130
40373
40374
40375
40376
40377
40378
40379
40496