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

Query time 0.00571

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "864.55"
    },
    "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": "82.45",
            "eval_cost": "86.89",
            "prefix_cost": "864.55",
            "data_read_per_join": "2M"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ],
          "attached_condition": "(`maniamall_cscart`.`a`.`id_path` like <cache>(concat('290/1035','/%')))"
        }
      }
    ]
  }
}

Result

category_id
1044
1088
1090
1205
1207
1114
1279
6794
1131
1318
1201
1264
1266
2382
2699
13057
1306
1310
1325
1345
1375
19445
19446
19491
19568
20830
20923
21141
21171
19497
19540
19541
19542
19548
19577
19827
20792
20827
20834
20924
20925
20927
20930
20961
21089
21091
21115
19566
19756
20793
20794
20835
20968
21157
20928
20929
21583
20975
21323
23230
23276
23252
23255
23256
23855
23258
23262
23840
28622
23263
23264
23265
23277
23847
23282
23399
23306
23307
23323
23332
23393
2616
2642
2643
2686
2696
2689
2700
2702
2706
2790
29881
5111
23462
23463
23464
23465
5112
5113
5114
5115
5116
5117
5118
5119
5120
5121
5122
5123
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5366
5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
7213
5365
5371
5375
7212
5377
7206
7207
5384
5385
5386
5417
5432
7230
7231
7232
7233
5388
7181
7182
5390
5406
7210
7211
5747
5748
5749
5750
5751
5752
5753
5754
5755
7183
7184
7185
7186
7187
7188
7189
7193
7194
7197
7198
7190
7191
7192
7195
7196
7199
7200
7201
7202
7203
7204
7205
7208
7209
7214
7215
7216
7217
5187
5716
5772
6628
6795
7122
7162
8780
8781
8908