Slow Union All and Left outer join query

Question

I want to create a resultset based on three different sources (tables):

• reports (540 records)
• business terms (300 records)
• attributes (500k records)

The following SQL works fine:

SELECT report.code AS code, report.name AS name, 'report' AS type, report.class AS class 
FROM report
UNION ALL
SELECT businessterm.id AS code, businessterm.business_term AS name, 'business_term' AS type, businessterm.class AS class 
FROM businessterm
UNION ALL
SELECT attributes.table_name AS code, attributes.column_name AS name, 'attributes' AS type, 'P0' AS class
FROM attributes;

However, for the attributes-table, the class-element must be joined from another table (attributes_class, 600k records).

To my knowledge, the final SQL should look something like this:

SELECT report.code AS code, report.name AS name, 'report' AS type, report.class AS class 
FROM report
UNION ALL
SELECT businessterm.id AS code, businessterm.business_term AS name, 'business_term' AS type, businessterm.class AS class 
FROM businessterm
UNION ALL
SELECT a.table_name AS code, a.column_name AS name, 'attributes' AS type, a.class AS class
FROM (
    SELECT attributes.table_name, attributes.column_name, attributes_class.class as class
    FROM attributes
    LEFT JOIN attributes_class ON attributes_class.id = attributes.class_id
    ) AS a;

When I execute all the separate queries, they run absolutely fine. However, when combined to a single query, it keeps running.

Answer 1

Analyzing the final query with explain and analyze provided the solution: in table attributes it had to create an index for the field that was used in the join-clause (class_id).

SELECT r.code AS code, r.name AS name, 'report' AS type, r.class AS class 
FROM report r
UNION ALL
SELECT b.id AS code, b.business_term AS name, 'business_term' AS type, b.class AS class 
FROM businessterm b
UNION ALL
SELECT a.table_name AS code, a.column_name AS name, 'attributes' AS type, ac.class AS class
FROM attributes a
LEFT JOIN attributes_class ac ON ac.id = a.class_id;