- 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