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)