I recently discovered that MySQL before 5.6 (5.5 being the latest available on RDS, of course) does not honor indexes if they should be invoked for a subquery, e.g.
select whatever from wherever where user_id in (select id from users where somethingorother like '%lol%');
Got an index on user_id? Too bad. Ignored.
If you precompute the values, though?
select whatever from wherever where user_id in (1, 2, 3);
Let's say your inner query returns n rows and wherever contains m rows. And your index is a B-tree, so you can hit it once in O(log m).
If you use a nested loop join, where you hit the index once for each inner query result, that's O(n*log m). If on the other hand you do a hash join, skipping the index but doing a full table scan of wherever, the complexity is O(m+n). So which is the faster choice depends on how many rows the inner query returns.
If you want to plan up front, before you know what m is, how would you decide which join to use?
select whatever from wherever where user_id in (select id from users where somethingorother like '%lol%');
Got an index on user_id? Too bad. Ignored.
If you precompute the values, though?
select whatever from wherever where user_id in (1, 2, 3);
Sweet, I love indexes! I'll definitely use them.