How to create on-the-fly index on derived table?

[Nathan Cheng]

I have not been able to find anything in the documentation about how to create an index on a derived table. Is it possible?

I have a query that looks like this:

SELECT ...
FROM a
LEFT JOIN (
SELECT some_id, ... FROM ...
) AS b ON b.some_id = a.some_id

Both a and b have several hundred thousand rows, so the query is taking forever. I let it run for 15 mins or so and killed it.

Then I did this, and it finished in about 0.01 seconds:

CREATE TEMPORARY TABLE b SELECT some_id, ... FROM ...;
ALTER TABLE b ADD INDEX( some_id );
SELECT ... FROM a LEFT JOIN b ON b.some_id = a.some_id;

Of course, for the sake of a simple example I have cut a lot of stuff out of the queries, but this was the basic structure.

Is there anyway to do the first query but force the creation of INDEX( some_id ) on the derived table b? Why isn't this already being done automatically?