SELECT 
  c.id_path, 
  p.link_type 
FROM 
  cscart_categories as c 
  LEFT JOIN cscart_products_categories as p ON p.category_id = c.category_id 
WHERE 
  p.product_id = 18519 
ORDER BY 
  p.category_position DESC, 
  p.category_id DESC

Query time 0.00022

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.00869848,
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "p.category_position desc, p.category_id desc",
            "table": {
              "table_name": "p",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "pt"],
              "key": "pt",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["const"],
              "loops": 1,
              "rows": 2,
              "cost": 0.0052712,
              "filtered": 100,
              "attached_condition": "p.product_id <=> 18519"
            }
          }
        }
      },
      {
        "table": {
          "table_name": "c",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["u508912950_dev_2025.p.category_id"],
          "loops": 2,
          "rows": 1,
          "cost": 0.00342728,
          "filtered": 100
        }
      }
    ]
  }
}