0

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?

Improve this question
2
  • Do you want UNION [DISTINCT] or UNION ALL. That is to say, is there a chance that tablea(a_href,a_name), is duplicated as tableb(b_href,b_name)
    – Evan Carroll
    Commented Dec 21, 2016 at 18:50
  • They should not have duplicates in the first place, but if they do, then I would say DISTINCT
    – Mario Stoilov
    Commented Dec 21, 2016 at 18:57

1 Answer 1

Reset to default
1

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.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.