MySQL Forums
Forum List  »  MyISAM

Re: Performance by Selections
Posted by: Greg Mundt
Date: February 21, 2019 06:58AM

Here is the information requested.

+------------+
| @@version |
+------------+
| 5.5.52-cll |
+------------+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT L.ORD_Item, SUM(L.ORD_Qty), SUM(L.ORD_Qty * L.ORD_Price) / SUM(L.ORD_Qty) AS WEIGHTED_PRICE, SUM(L.ORD_Qty * L.ORD_Price) AS SALES, COUNT(L.ORD_Item) AS TXNS, L.ORD_Item_Class, L.ORD_Desc FROM pos_ord_headers as H LEFT JOIN pos_ord_lines as L ON H.ORD_ID=L.ORD_ID WHERE H.ORD_Store='0110' AND (H.ORD_Date BETWEEN '2018-01-01' AND '2018-12-31') GROUP BY L.ORD_Item ASC;
+----+-------------+-------+-------+---------------------+------------+---------+----------------------+--------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------------+------------+---------+----------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | H | range | Store_Date,ORD_Date | Store_Date | 9 | NULL | 108011 | 75.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | L | ref | PRIMARY | PRIMARY | 22 | kilwins_pos.H.ORD_ID | 832690 | 100.00 | |
+----+-------------+-------+-------+---------------------+------------+---------+----------------------+--------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> EXPLAIN EXTENDED SELECT L.ORD_Item, SUM(L.ORD_Qty), SUM(L.ORD_Qty * L.ORD_Price) / SUM(L.ORD_Qty) AS WEIGHTED_PRICE, SUM(L.ORD_Qty * L.ORD_Price) AS SALES, COUNT(L.ORD_Item) AS TXNS, L.ORD_Item_Class, L.ORD_Desc FROM pos_ord_headers as H LEFT JOIN pos_ord_lines as L ON H.ORD_ID=L.ORD_ID WHERE H.ORD_Store='0110' AND (H.ORD_Date BETWEEN '2018-01-01' AND '2018-12-31') AND L.ORD_ITEM='4020' GROUP BY L.ORD_Item ASC;
+----+-------------+-------+--------+-----------------------------+---------+---------+----------------------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------------------+---------+---------+----------------------+-------+----------+-------------+
| 1 | SIMPLE | L | ref | PRIMARY,Items | Items | 22 | const | 47182 | 100.00 | Using where |
| 1 | SIMPLE | H | eq_ref | PRIMARY,Store_Date,ORD_Date | PRIMARY | 22 | kilwins_pos.L.ORD_ID | 1 | 100.00 | Using where |
+----+-------------+-------+--------+-----------------------------+---------+---------+----------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
1269
February 19, 2019 09:35AM
863
February 19, 2019 12:02PM
Re: Performance by Selections
804
February 21, 2019 06:58AM
704
February 21, 2019 01:13PM
767
February 28, 2019 03:56PM
732
February 28, 2019 05:10PM
744
March 04, 2019 10:14AM
726
March 04, 2019 11:08AM
760
March 22, 2019 06:09AM
690
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.