Thanks for replying. I remember having to use those sort of tricks to get more performance out of mysql queries in the past.
I use postgres these days and my system characteristics are such that I don't have to worry about query performance so much. Though I have found that I've needed similar tactics sometimes with postgres too.
We have tried a few different alternatives, including the join, and found that this sub-query option actually works better.
A sub query forces the database to execute the first query on routes using the index, then use the result for the second query on sites. It leave nothing for the interpretation of the optimizer which may decide to so some silly thing like full scan on the sites table before performing the join.