MySQL Query Optimization – Indexing

Indexing is the most important tool you have for speeding up queries. Other techniques are available to you, too, but generally the one thing that makes the most difference is the proper use of indexes.

MySQL uses indexes in several ways:

* As just described, indexes are used to speed up searches for rows matching terms of a WHERE clause or rows that match rows in other tables when performing joins.
* For queries that use the MIN() or MAX() functions, the smallest or largest value in an indexed column can be found quickly without examining every row.
* MySQL can often use indexes to perform sorting and grouping operations quickly for ORDER BY and GROUP BY clauses.
* Sometimes MySQL can use an index to reading all the information required for a query. Suppose that you’re selecting values from an indexed numeric column in a MyISAM table, and you’re not selecting other columns from the table. In this case, when MySQL reads an index value from the index file, it obtains the same value that it would get by reading the data file. There’s no reason to read values twice, so the data file need not even be consulted.

Ref:- http://www.informit.com/articles/article.aspx?p=377652