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.