SELECT 
  category_id, 
  parent_id 
FROM 
  cscart_categories 
WHERE 
  parent_id IN(
    2709, 2778, 2922, 2934, 2945, 2959, 2974, 
    2981, 3004, 3012, 3037, 3430, 3048, 
    3181, 3190, 3191, 3200, 3220, 3236, 
    3237, 3242, 3243, 3244, 3250, 3366, 
    3413
  ) 
  AND category_id != 414

Query time 0.00055

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "44.31"
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "parent",
        "p_category_id"
      ],
      "key": "parent",
      "used_key_parts": [
        "parent_id"
      ],
      "key_length": "3",
      "rows_examined_per_scan": 84,
      "rows_produced_per_join": 84,
      "filtered": "100.00",
      "index_condition": "(`s2cart`.`cscart_categories`.`parent_id` in (2709,2778,2922,2934,2945,2959,2974,2981,3004,3012,3037,3430,3048,3181,3190,3191,3200,3220,3236,3237,3242,3243,3244,3250,3366,3413))",
      "cost_info": {
        "read_cost": "35.91",
        "eval_cost": "8.40",
        "prefix_cost": "44.31",
        "data_read_per_join": "297K"
      },
      "used_columns": [
        "category_id",
        "parent_id"
      ],
      "attached_condition": "(`s2cart`.`cscart_categories`.`category_id` <> 414)"
    }
  }
}

Result

category_id parent_id
2710 2709
2711 2709
2712 2709
2713 2709
2946 2945
2960 2959
2961 2959
2962 2959
2963 2959
2964 2959
2965 2959
2966 2959
2967 2959
2968 2959
2969 2959
2970 2959
2971 2959
2972 2959
2973 2959
2975 2974
2976 2974
2977 2974
2978 2974
2979 2974
2980 2974
2982 2981
2983 2981
3013 3012
3014 3012
3015 3012
3049 3048
3050 3048
3051 3048
3052 3048
3053 3048
3054 3048
3192 3191
3193 3191
3194 3191
3195 3191
3196 3191
3197 3191
3198 3191
3201 3200
3202 3200
3203 3200
3204 3200
3205 3200
3206 3200
3207 3200
3208 3200
3209 3200
3210 3200
3211 3200
3221 3220
3222 3220
3223 3220
3224 3220
3225 3220
3226 3220
3227 3220
3228 3220
3229 3220
3230 3220
3231 3220
3232 3220
3233 3220
3234 3220
3235 3220
3238 3237
3239 3237
3240 3237
3241 3237
3367 3366