How to optimise multiple joins with OR?
As jkavalik suggested in his comment, I tried UNION and it worked great. The performance improvement was astronomical.

SELECT
  COUNT(a.nid) AS cnt, a.nid, a.title
FROM
  node a /* Airport */
JOIN
  (
    (SELECT
      dep.nid
    FROM
      departure_field dep
    JOIN
      node f
    ON
      f.nid = dep.flight_node_id
    WHERE
      f.type = 'flight' AND f.uid = 100
    )
  UNION ALL
    (SELECT
      arr.nid
    FROM
      arrival_field are
    JOIN
      node f
    ON
      f.nid = arr.arrival_node_id
    WHERE
      f.type = 'flight' AND f.uid = 100
    )
  ) airport
ON
  airport.id = a.nid
GROUP BY
  a.nid
ORDER BY
  a.cnt DESC


Note that in my case UNION ALL was the key, because it includes duplicates from both arrivals and departures. And this was important because I wanted to count how much times each airport was visited. Without ALL each airport would show up only once as simple UNION removes duplicates.