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

Query time 0.00840

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "368.89"
    },
    "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": "3K"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ]
        }
      },
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "rows_examined_per_scan": 3444,
          "rows_produced_per_join": 382,
          "filtered": "11.11",
          "cost_info": {
            "read_cost": "24.49",
            "eval_cost": "38.26",
            "prefix_cost": "368.89",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ],
          "attached_condition": "(`s2cart`.`a`.`id_path` like <cache>(concat('2984','/%')))"
        }
      }
    ]
  }
}

Result

category_id
2985
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3005
3006
3007
3134
3151
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3343
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412