Re: Top 10 airports referenced from a table of flights

[Jani Patokallio]

Works! And another factor of ten or so faster, even after throwing in a join to get the airport names etc:

select a.name, a.iata, sum(x.ct) as count from
( select src_apid as apid, count(*) as ct
from flights
GROUP BY src_apid
UNION ALL
select dst_apid as apid, count(*) as ct
from flights
GROUP BY dst_apid
) x, airports as a
where a.apid=x.apid
group by x.apid
order by count desc
limit 10;

~4s with previous query, now down to 0.33s.