Need help with difficult slow TRANSACTION (UNION )

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.

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