Merge indexes in union I have two tables:

Table A:
a_id, a_href, a_name,......

Table B:
b_id, b_href, b_name......


Both tables have indexes on their href column. I intend to union these tables and join them on another, something like

Select c_id, u.name FROM TableC
Left join (Select a_href AS href, a_name AS name FROM TableA
           UNION
           SELECT b_href AS href, b_name AS name FROM TableB) AS u ON u.href = c_href


I want to preserve the index on href columns in the union, so my join is fast. Do I have to dynamically create an index for that union, or is there a better way to do this?
A UNION will not have an index.  A "derived table", which is what you have inside the parens, may be given an index -- it depends first on which version of MySQL you are running.  Please provide EXPLAIN SELECT ....

Get rid of LEFT so that the Optimizer can start with the UNION.  And add INDEX(href) in C so that the JOIN can work the other direction.

If you really need NULL for u.name, then you need the LEFT, and there is no hope for optimizing.

(The default for UNION is UNION DISTINCT, which is slower than UNION ALL.)