First some of the things that you need to use and
Order by Optimization
Group by Optimization
Update: Updated errors.
Now some details that are usually missed. GROUP BY does sorting unless you tell mysql not to. GROUP BY has two optimization methods, loose index scan, …