MySQL Forums
Forum List  »  MyISAM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance by Selections
1260
February 19, 2019 09:35AM
858
February 19, 2019 12:02PM
799
February 21, 2019 06:58AM
700
February 21, 2019 01:13PM
762
February 28, 2019 03:56PM
728
February 28, 2019 05:10PM
738
March 04, 2019 10:14AM
721
March 04, 2019 11:08AM
756
March 22, 2019 06:09AM
685
March 22, 2019 09:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.