Parser and Optimizer

The MySQL server receives queries in the SQL format. Once a query is received, it first needs to be parsed, which involves translating it from what is essentially a textual format into a combination of internal binary structures that can be easily manipulated by the optimizer.

MySQL's optimizer has several important tasks:
. Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
. For each table, decide whether a table scan is better than reading on a key. If there are a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
. Determine the order in which tables should be joined when more than one table is present in the query.
. Rewrite the WHERE clause to eliminate dead code, reducing the unnecessary computations and changing the constraints whenever possible to open the way for using keys.
. Eliminate unused tables from the join.
. Determine whether keys can be  used for ORDER BY and GROUP BY.
. Attempt to replace an outer join with an inner join.
. Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
. Merge views.

Using EXPLAIN to Understand the Optimizer
The MySQL EXPLAIN command tells the optimizer to show its query plan.
E.g,
EXPLAIN SELECT ... \G
Understanding the output of EXPLAIN

EXPLAIN FIELD	DESCRIPTION
id	QueryID. Meaningful only when subqueries are used.
select_type	Indicates what happens with the result set retrieved from the table. A join not involving subqueries or UNION will have this value set to simple. See the upcoming section "Select types" for details.
table	The alias the table is referenced by in the query. If no alias is used, the real name of this table.
type	The method used for retrieving the records from the table. See the upcoming section "Record access types" for details.
possible_keys	A list of keys that can be used in conjunction with the WHERE clause to retrieve the records from this table.
key	The name of the key used for retrieving the records. When index_merge optimization is used, contains a list of keys.
key_len	The length of the key used in a query. This does not have to be the full length of the key - it is possible to use only a key prefix.
ref	A list of fields from other tables whose values are involved in an index lookup in this table.
rows	Average estimated number of records in this table to be retrieved on each join iteration.
Extra	Additional comments on the optimization strategy. See the section "Extra field" for details.