The MySQL explain plan shows how the MySQL optimizer has decided to run a SELECT statement and access the data.
The command syntax for viewing the EXPLAIN output is:
explain select statement
for example:
Let us analyze the following 2 queries:
SELECT * FROM Country WHERE Name = 'France';
SELECT * FROM Country WHERE Code = 'FRA';
Both queries produce the same output (information about the country of France), but they are not equally efficient. Using EXPLAIN we can find why?
mysql> EXPLAIN SELECT * FROM Country WHERE Name ='France"\G
 1. row ***************************
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 239
Extra: Using where
mysql> EXPLAIN SELECT * FROM Country WHERE Code = 'FRA'\G
1. row ***************************
table: Country
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
EXPLAIN produces several columns of information. In the example just shown, NULL in the possible_keys and key columns shows for the first query that no index is considered available or usable for processing the query. For the second query, the table's PRIMARY KEY column (the Code column that contains three-letter country codes) can be used, and is in fact the one that the optimizer would choose. The rows column of the EXPLAIN output shows the effect of this difference. Its value indicates the number of rows that MySQL estimates it will need to examine while processing the query:

• For the first query, the value is 239, which happens to be the number of rows in the Country table. This value indicates that MySQL would scan all rows of the table, which is inefficient.
• For the second query, only one row need be examined. This is because MySQL can use the table's primary key to go directly to the single relevant row.

This example briefly indicates the kind of useful information that EXPLAIN can provide, even for simple queries. The conclusion to draw is that, if possible, you should use the Code column rather than the Name column to look up Country table records. However, the real power of EXPLAIN lies in what it can tell you about joins---SELECT queries that use multiple tables.
EXPLAIN is especially important for join analysis because they have such enormous potential to increase the amount of processing the server must do. If you select from a table with a thousand rows, the server might need to scan all one thousand rows in the worst case. But if you perform a join between two tables with a thousand rows each, the server might need to examine every possible combination of rows, which is one million combinations. That's a much worse worst case. EXPLAIN can help you reduce the work the server must do to process such a query, so it's well worth using.
The columns in the Explain show the following:

• id The SELECT identifier. This is the sequential number of the SELECT within the query.
• select_type The type of SELECT, which can be any of those shown in the following table:

• table The table to which the row of output refers.
• type The join type
• possible_keysThe possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
• KeyThe key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query
• key_lenThe key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL
• refThe ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
• rowsThe rows column indicates the number of rows MySQL believes it must examine to execute the query.
• Extra This column contains additional information about how MySQL resolves the query