[MySolution] Group By, Two Approaches, Performance Compare
Look at two queries:\n\nQuery A:\n\n# Write your MySQL query statement below\nselect\n    event_day as day, emp_id\n    , sum(out_time) - sum(in_time) as total_
Look at two queries:\n\nQuery A:\n```\n# Write your MySQL query statement below\nselect\n    event_day as day, emp_id\n    , sum(out_time) - sum(in_time) as total_time\nfrom \n    Employees\ngroup by \n    event_day, emp_id\n```\n\nQuery B:\n```\nselect\n    event_day as day, emp_id\n    , sum(out_time-in_time) as total_time\nfrom \n    Employees\ngroup by \n    event_day, emp_id\n```\n\nThere is a post (link below) says these are similar approaches, but Query B is faster x2 A. It might be due to connection (from a user\'s comment). At first, I agreed with the comment. However, I was curious and I submited 10 times for each query. Here is the average runtime:\n\n![image](https://assets.leetcode.com/users/images/4e6f6a0e-2d71-4cdd-a2ab-1e293980e086_1614500203.3466053.png)\n\nQuery B is still faster than A, although it\'s just 2%. But the data is quite small, what about 500k, or 1m records? Therefore I tested them with a similar data (from my company - sorry I cannot share) in bigquery. It has over 500k rows. Here is the results:\n\n![image](https://assets.leetcode.com/users/images/50657f3a-d58b-4b56-a4cd-17792d143761_1614500770.0775092.png)\n\nI won\'t go to details about how these numbers were calculated. Simply speaking, **lower** Slot time consumed and Bytes shuffled indicate **higher** performance and vice versa. As you can see, both queries process same amount of data (13.5 MiB), but Query B performs better than A. In my opinion, with query A, we are asking more works.\n\nQuery A: Sum (1st col) -> Sum (2nd col) -> Subtract\nQuery B: Subtract -> Sum\n\nThat\'s why query B has better performance than A.\n\nPlease correct me if I\'m wrong. Thanks.\n\n*Thanks to a post [Unexpected Optimization](https://leetcode.com/problems/find-total-time-spent-by-each-employee/discuss/1047716/Unexpected-Optimization) by florin5 and comment from YenniferYe97. \nThis is their original opinions, I just would like to expand it a bit and share to you guys.*
[]