Slow UNION ALL query with MySQL

Dear experts,

I came across the situation where my query became extremely slow when I have a UNION ALL.

Here is my query:

SELECT po.pur_num, po.s_no, po.part_num, po.qty_ord, po.cost, 'N', 'insert new line'
FROM (SELECT scHd.po_num, sc.s_no, sc.part_num, sc.qty, sc.unit_pr
        FROM (SELECT hd.id, hd.po_num
                  FROM purchase.sales_contract_hd hd
                  JOIN receiving.dynamic_po_hd dHd ON hd.po_num = dHd.pur_num
                  WHERE dHd.po_stat = '') scHd
        JOIN purchase.sales_contract sc ON scHd.id = sc.scHd_id
        UNION ALL
        SELECT po_num, s_no, part_num, qty, unit_pr
        FROM sc_sbt_log) s
RIGHT JOIN (SELECT pur_num, s_no, part_num, qty_ord, cost
                  FROM receiving.dynamic_po) po ON s.po_num = po.pur_num AND s.part_num = po.part_num
WHERE s.part_num IS NULL

If I remove the section with UNION ALL from above, the performance is good again.
UNION ALL
        SELECT po_num, s_no, part_num, qty, unit_pr
        FROM sc_sbt_log

Reason I coded the UNION ALL because I needed the extra information along with the information that I have. The performance with UNION ALL was like few hundred seconds vs no UNION ALL which was 2.17 seconds.

Is there anyway to boost my performance? Let me know if I need to provide more information. Thanks
Kinderly WadeprogrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I think you confuse MySQL server by using the aliases for the columns here:
SELECT scHd.po_num, sc.s_no, sc.part_num, sc.qty, sc.unit_pr

Open in new window

Try adding a new alias for the UNIONed query, like this:
SELECT po.pur_num, po.s_no, po.part_num, po.qty_ord, po.cost, 'N', 'insert new line'
FROM (SELECT s.po_num, s.s_no, s.part_num, s.qty, s.unit_pr
      FROM
      (
        SELECT scHd.po_num, sc.s_no, sc.part_num, sc.qty, sc.unit_pr
        FROM (SELECT hd.id, hd.po_num
                  FROM purchase.sales_contract_hd hd
                  JOIN receiving.dynamic_po_hd dHd ON hd.po_num = dHd.pur_num
                  WHERE dHd.po_stat = '') scHd
        JOIN purchase.sales_contract sc ON scHd.id = sc.scHd_id
        UNION ALL
        SELECT po_num, s_no, part_num, qty, unit_pr
        FROM sc_sbt_log) s) ss
RIGHT JOIN (SELECT pur_num, s_no, part_num, qty_ord, cost
                  FROM receiving.dynamic_po) po ON ss.po_num = po.pur_num AND ss.part_num = po.part_num
WHERE ss.part_num IS NULL

Open in new window

PortletPaulEE Topic AdvisorCommented:
I would move away from using right joins.

Try it the other way around:
SELECT
      po.pur_num
    , po.s_no
    , po.part_num
    , po.qty_ord
    , po.cost
    , 'N'
    , 'insert new line'
FROM receiving.dynamic_po po
LEFT JOIN (
            SELECT
                  scHd.po_num
                , sc.part_num
            FROM (
                  SELECT
                        hd.id
                      , hd.po_num
                  FROM purchase.sales_contract_hd hd
                  JOIN receiving.dynamic_po_hd dHd
                        ON hd.po_num = dHd.pur_num
                  WHERE dHd.po_stat = ''
            ) scHd
            JOIN purchase.sales_contract sc
                  ON scHd.id = sc.scHd_id
            UNION ALL
            SELECT
                  po_num
                , part_num
            FROM sc_sbt_log
           ) s
              ON s.po_num = po.pur_num
              AND s.part_num = po.part_num
WHERE s.part_num IS NULL

Open in new window

PortletPaulEE Topic AdvisorCommented:
an alternative approach would be to use NOT EXISTS

I think this should be functionally equivalent
SELECT
      po.pur_num
    , po.s_no
    , po.part_num
    , po.qty_ord
    , po.cost
    , 'N'
    , 'insert new line'
FROM receiving.dynamic_po po
WHERE NOT EXISTS (
                  SELECT NULL
                  FROM purchase.sales_contract_hd hd
                  JOIN receiving.dynamic_po_hd dHd
                        ON hd.po_num = dHd.pur_num
                  JOIN purchase.sales_contract sc
                        ON hd.id = sc.scHd_id
                  WHERE dHd.po_stat = ''
                  AND hd.po_num = po.pur_num
                  AND hd.part_num = po.part_num
                  )
AND NOT EXISTS (
                  SELECT NULL
                  FROM sc_sbt_log ssl
                  AND ssl.po_num = po.pur_num
                  AND ssl.part_num = po.part_num
                )
;

Open in new window

Tomas Helgi JohannssonCommented:
Hi!

>If I remove the section with UNION ALL from above, the performance is good again.
>UNION ALL
>        SELECT po_num, s_no, part_num, qty, unit_pr
>        FROM sc_sbt_log
>
>Reason I coded the UNION ALL because I needed the extra information along with the information >that I have. The performance with UNION ALL was like few hundred seconds vs no UNION ALL which >was 2.17 seconds.

This is obvious as the select you removed produces a TABLESCAN on sc_sbt_log table as there are no where clause with indexable predicates in that select statement.

Regards,
     Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.