SELECT 
  category_id, 
  parent_id 
FROM 
  cscart_categories 
WHERE 
  parent_id IN(
    1870, 1854, 1852, 1603, 1624, 1556, 1626, 
    1553, 1849, 1612, 1850, 1851, 1853, 
    1855, 1856, 1858, 1859, 1860, 1861, 
    1862, 1863, 1864, 1865, 1866, 1867, 
    1868, 1869, 1872, 1873
  ) 
  AND category_id != 414

Query time 0.00070

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "23.91"
    },
    "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": 37,
      "rows_produced_per_join": 37,
      "filtered": "100.00",
      "index_condition": "(`s2cart`.`cscart_categories`.`parent_id` in (1870,1854,1852,1603,1624,1556,1626,1553,1849,1612,1850,1851,1853,1855,1856,1858,1859,1860,1861,1862,1863,1864,1865,1866,1867,1868,1869,1872,1873))",
      "cost_info": {
        "read_cost": "20.21",
        "eval_cost": "3.70",
        "prefix_cost": "23.91",
        "data_read_per_join": "130K"
      },
      "used_columns": [
        "category_id",
        "parent_id"
      ],
      "attached_condition": "(`s2cart`.`cscart_categories`.`category_id` <> 414)"
    }
  }
}

Result

category_id parent_id
1554 1553
1555 1553
1557 1556
1558 1556
1559 1556
1560 1556
1561 1556
1562 1556
1563 1556
1564 1556
1874 1873
1875 1873