Merge indexes in union
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.)