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

Query time 0.00724

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

Result

category_id
1996
1997
1998
1999
2000
2001
2003
2004
2006
2086
2087
2088
2089
2090
2091
2130
2131
2132
2133
2134
2135
2138
2139
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2259
2270
2271
2272
2279
2280
2281
2344
2117
2218
2137
2260
2273
2256
2258
2002
2257
2342
1983
2005
2118
2015
2276
2300
1994
2143
2261
2294
2072
2278
3551
2129
2269
2315
1995
2136
2290
2320
2343