how does MySQL implement the "group by"?
- by user188916
I read from the MySQL Reference Manual and find that when it can take use of index,it just do index scan,other it will create tmp tables and do things like filesort.
And I also read from other article that the "Group By" result will sort by group by columns by default,if "order by null" clause added,it won't don filesort.
The difference can be found from the "explain ..." clause.
so my problem is:what is the difference between "group by" clause that with "order by null" and which doesn't have?
I try to use profiling to see what mysql do on the background,and only see result like:
result for group clause without order by null:
|preparing | 0.000016 |
| Creating tmp table | 0.000048 |
| executing | 0.000009 |
| Copying to tmp table | 0.000109 |
**| Sorting result | 0.000023 |**
| Sending data | 0.000027 |
result for clause with "order by null":
preparing | 0.000016 |
| Creating tmp table | 0.000052 |
| executing | 0.000009 |
| Copying to tmp table | 0.000114 |
| Sending data | 0.000028 |
So I guess what MySQL do when the "order by null" added,it does not use filesort algorithm,maybe when it creates the tmp table,it uses index as well,and then use the index to do group by operation,when completed,it just read result from the table rows and does not sort the result.
But my original opinion is that MySQL can use quicksort to sort the items and then do group by,so the result will be sorted as well.
Any opinion appreciated,thanks.
