mysql indexing notes

Show indexes of a table

SHOW INDEX FROM table_name;

Create index

CREATE INDEX index_name ON table_name(col_ame)

  Drop index

DROP INDEX index_name ON table_name

Detail explaination of all fields

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:

SIMPLE	Simple SELECT (no UNION / subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table SELECT (subquery in FROM clause)

table
The table to which the row of output refers.

type
The join type

possible_keys
The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.

Key
The 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_len
The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL

ref
The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

rows
The 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

More information about EXPLAIN

http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html