SELECT 
  category_id, 
  parent_id 
FROM 
  cscart_categories 
WHERE 
  parent_id IN(
    6574, 6667, 6679, 6685, 6699, 6749, 6799, 
    6805, 6813, 6820, 6824
  )

Query time 0.00033

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "11.43"
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "range",
      "possible_keys": [
        "parent"
      ],
      "key": "parent",
      "used_key_parts": [
        "parent_id"
      ],
      "key_length": "3",
      "rows_examined_per_scan": 26,
      "rows_produced_per_join": 26,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "6.23",
        "eval_cost": "5.20",
        "prefix_cost": "11.43",
        "data_read_per_join": "154K"
      },
      "used_columns": [
        "category_id",
        "parent_id"
      ],
      "attached_condition": "(`webmarco`.`cscart_categories`.`parent_id` in (6574,6667,6679,6685,6699,6749,6799,6805,6813,6820,6824))"
    }
  }
}

Result

category_id parent_id
6580 6574
6590 6574
6601 6574
6608 6574
6614 6574
6618 6574
6629 6574
6639 6574
6642 6574
6658 6574
6711 6699
6729 6699
6742 6699
6760 6749
6768 6749
6776 6749
6785 6749
6791 6749