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