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

Query time 0.00219

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1494.60"
    },
    "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.20",
            "prefix_cost": "0.00",
            "data_read_per_join": "5K"
          },
          "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": 6988,
          "rows_produced_per_join": 776,
          "filtered": "11.11",
          "using_index": true,
          "cost_info": {
            "read_cost": "97.00",
            "eval_cost": "155.27",
            "prefix_cost": "1494.60",
            "data_read_per_join": "4M"
          },
          "used_columns": [
            "category_id",
            "id_path"
          ],
          "attached_condition": "(`webmarco`.`a`.`id_path` like <cache>(concat('2910/3105/3104','/%')))"
        }
      }
    ]
  }
}

Result

category_id
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3161
3162
3163
3164
3165
3166
3167
3168
3169
3724
3728
3170
3171
3172
3173
3174
3175
3176
3177
3131
3132
3133
3134
3135
3136
3137