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

Query time 0.00530

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

Result

category_id
563
562
561
560
559
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
538
537
536
534
535
533
532
528
529
530
531