# Need help with difficult slow TRANSACTION (UNION )

**URL:** <https://forums.percona.com/t/need-help-with-difficult-slow-transaction-union/977>\
**Category:** Other MySQL® Questions\
**Created:** [October 27, 2008, 1:59pm UTC](https://forums.percona.com/t/need-help-with-difficult-slow-transaction-union/977 "2008-10-27T13:59:36Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ballouc](https://avatars.discourse-cdn.com/v4/letter/b/ac8455/32.png) [@ballouc](https://forums.percona.com/u/ballouc)\
**Post date:** [October 27, 2008, 1:59pm UTC](https://forums.percona.com/t/need-help-with-difficult-slow-transaction-union/977/1 "2008-10-27T13:59:36Z")

</div>

I’m working on an e-commerce site tying into the accounting software SBT. The SBT setup has purchase transactions going through the transaction table WBMAST to push data up to the SQL Server tables SOMAST/SOADDR. There is a 5 minute delay between this push and SBT calling a synchronization method to grab the new order.

The problem I’m experiencing with this is that I must query both the WBMAST and SOMAST table to ensure all orders are being accounted for on the “View All Invoices” page. Long story short the following query I have mustered together is running very slow (filesort, ugh) as you’ll see from the explain.

(SELECT GREATEST(COALESCE(@found := -1, custno), COALESCE(custno, @found := -1)) AS custno,  
“wbmast” AS tbl,  
wb.sodate as m\_shipdate,  
wb.ordate as m\_orderdate,  
wb.shipvia as m\_shipmethod,  
wb.pterms as m\_paymentterms,  
wb.ordamt as m\_totalprice,  
wb.tax as m\_tax,  
wb.ponum as m\_purchaseordernum  
FROM wbmast wb  
WHERE custno = “TESTBL”  
AND wb.sodate \> “2008-08-27 02:08:01”)  
UNION  
(SELECT custno as custno, “somast” AS tbl,  
sm.sodate as m\_shipdate,  
sm.ordate as m\_orderdate,  
sm.shipvia as m\_shipmethod,  
sm.pterms as m\_paymentterms,  
sm.ordamt as m\_totalprice,  
sm.taxsamt as m\_tax,  
sm.ponum as m\_purchaseordernum  
FROM somast sm  
WHERE sm.custno = “TESTBL”  
AND sm.sodate \> “2008-08-27 02:08:01”)  
UNION  
(SELECT 1, “”, 1, 1, 1, 1, 1, 1, 1  
FROM dual  
WHERE ( @found := null ) IS NOT NULL)  
ORDER BY m\_shipdate DESC  
LIMIT 0,50

±—±-------------±-------------±-----±--------------± -----±--------±-----±-----±-----------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±-------------±-------------±-----±--------------± -----±--------±-----±-----±-----------------+  
| 1 | PRIMARY | wb | ALL | NULL | NULL | NULL | NULL | 1 | Using where |  
| 2 | UNION | sm | ALL | NULL | NULL | NULL | NULL | 1870 | Using where |  
| 3 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |  
| NULL | UNION RESULT | \<union1,2,3\> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |  
±—±-------------±-------------±-----±--------------± -----±--------±-----±-----±-----------------+

Could somebody please help point me in the right direction? Is there a better/faster way of determining the union of these two tables to ensure I have the most up to date invoices? As it stands now if any rows exist in the WBMAST table the query ends and only those rows are returned. I haven’t created an index on the tables yet as I’m just playing with a subset of data to ensure everything is working properly.

---

<div class="post-metadata">

**Author:** ![artur8ur](https://avatars.discourse-cdn.com/v4/letter/a/9f8e36/32.png) [@artur8ur](https://forums.percona.com/u/artur8ur)\
**Post date:** [November 4, 2008, 9:48am UTC](https://forums.percona.com/t/need-help-with-difficult-slow-transaction-union/977/2 "2008-11-04T09:48:51Z")

</div>

Hi,

some comments:

– Push the Limit into the union:  
(SELECT … FROM wbmast wb WHERE … LIMIT 0,50 )  
UNION  
(SELECT … FROM somast sm WHERE … LIMIT 0,50 )  
UNION  
(SELECT … FROM dual WHERE … LIMIT 0,50 )  
ORDER BY m\_shipdate DESC  
LIMIT 0,50

– Your statements are not using any indexes… Add an index (custno, sodate) to wbmast and somast.

– I might be mistaken, but i think  
GREATEST(COALESCE(@found := -1, custno), COALESCE(custno, @found := -1)) AS custno,  
might be easier written:  
COALESCE(@found := -1, custno) → is always -1  
COALESCE(custno, @found := -1) → custno if custno != null, else -1  
GREATEST( -1, (custno if custno != null, else -1)) =\>  
custno if custno != null, else -1

This is:  
COALESCE(custno, -1) AS custno

– What is this table dual?  
(SELECT 1, “”, 1, 1, 1, 1, 1, 1, 1  
FROM dual  
WHERE ( @found := null ) IS NOT NULL)  
And as the explain states: the where is always false,… so there is never a result…  
Or do you mean @found IS NOT NULL