Author seems to be confused on how databases operate (especially since they really should just be using a KV store like Redis), because you're exactly right, the query would be the same as:

    select sites.* from sites join routes using (site_id) where route_id = ?

Assuming sites.site_id and routes.route_id are both primary keys, this query is going to perform identically using either syntax. It'll read 1 row from each table.

They could see a further performance gain by placing an index on routes (route_id, site_id) since the site_id could be retrieved from the b-tree and avoid a table hit entirely. But regardless, query syntax will not affect performance here.

You are right that this join query should be equivalent. However, with InnoDB there will not be much benefit from your suggested index. In InnoDB primary indexes are clustered. Hence, any column may be retrieved from the B-tree of the primary index.

Only if the columns are stored on-page with the index, which is not guaranteed -- it depends on what other columns exist in the table, etc.

Your question is valid, assuming you trust the query planner to make the right decision. However, the query planner in most databases is not perfect and can miss.

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.

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.

@yoava - can you comment on this?