Performance by Selections
Posted by:
Greg Mundt
Date: February 19, 2019 09:35AM
Not sure if this is the best place, but I have 2 MyISAM tables due to the fact that there are few writes, but many reads.
Table 1 - Order Header 35,500,000 rows Primary Index ORD_ID which is unique
Table 2 - Order Lines 85,225,000 rows Primary Index ORD_ID, ORD_Line which is unique
SELECT H.ORD_Store, L.ORD_Item, SUM(L.ORD_Qty * L.ORD_Price) / SUM(L.ORD_Qty) AS WEIGHTED_PRICE, SUM(L.ORD_Qty * L.ORD_Price) AS SALES, COUNT(ORD_ITEM) AS TXNS, L.ORD_Item_Class, L.ORD_Desc FROM ord_headers as H LEFT JOIN ord_lines as L ON H.ORD_ID=L.ORD_ID WHERE (H.ORD_Date BETWEEN '2018-01-01' AND '2018-12-31') GROUP BY ORD_Store, ORD_Item ASC
Returns within 10 seconds all of the results, but if I add AND L.ORD_Item='XXXXXX' to the where clause it will time out after 10 minutes. Why does any inclusion of the second file affect performance so drastically?