SELECT 
  category_id, 
  parent_id 
FROM 
  cscart_categories 
WHERE 
  parent_id IN(
    9728, 8086, 23768, 8085, 23785, 23510, 
    3220, 23507, 3215, 8092, 23738, 23524, 
    3321, 8087, 23769, 23792, 8134, 23506, 
    8709, 9721, 7908, 23733, 23776, 23777, 
    3169, 7898, 23496, 3170, 23494, 7897, 
    23495, 23525
  )

Query time 0.09411

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "11.77"
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "range",
      "possible_keys": [
        "parent"
      ],
      "key": "parent",
      "used_key_parts": [
        "parent_id"
      ],
      "key_length": "3",
      "rows_examined_per_scan": 57,
      "rows_produced_per_join": 57,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "6.07",
        "eval_cost": "5.70",
        "prefix_cost": "11.77",
        "data_read_per_join": "147K"
      },
      "used_columns": [
        "category_id",
        "parent_id"
      ],
      "attached_condition": "(`maniamall_cscart`.`cscart_categories`.`parent_id` in (9728,8086,23768,8085,23785,23510,3220,23507,3215,8092,23738,23524,3321,8087,23769,23792,8134,23506,8709,9721,7908,23733,23776,23777,3169,7898,23496,3170,23494,7897,23495,23525))"
    }
  }
}

Result

category_id parent_id
3170 3169
23494 3169
3215 3170
3220 3170
3321 3170
7897 3170
7908 3170
8085 3170
8086 3170
8087 3170
8092 3170
8134 3170
8709 3170
9721 3170
9728 3170
7898 7897
23495 23494
23506 23494
23507 23494
23510 23494
23524 23494
23733 23494
23738 23494
23768 23494
23769 23494
23776 23494
23777 23494
23785 23494
23792 23494
23496 23495
23525 23524