How MySQL Uses Indexes

• To find the rows matching a WHERE clause quickly
• To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
• To retrieve rows from other tables when performing joins
• To find the MIN() or MAX() value for a specific indexed column
• To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2)

Reference:

How MySQL Uses Indexes
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

ORDER BY Optimization
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

GROUP BY Optimization
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html