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

Query time 0.00272

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

Result

category_id
7846
7852
7885
7892
7900
7907
7915
7952
7961
7969
7988
7995
8009
8014
8022
8042
8055
8073
8086
8096
8103
8107
8128
8133
8142
8148
8158
8164
8166
8173
8180
8186
8189
8202
8208
8211
8224
8251
8259
8265
8271
8284
8291
8303
8310
8314
8319
8327
8332
8343
8348