MySQL Performance Tuning: EXPLAIN

EXPLAIN Types

system              The table has only one row 
const               At the most one matching row, treated as a constant 
eq_ref              One row per row from previous tables 
ref                 Several rows with matching index value 
ref_or_null         Like ref, plus NULL values 
index_merge         Several index searches are merged 
unique_subquery     Same as ref for some subqueries 
index_subquery      As above for non-unique indexes 
range               A range index scan 
index               The whole index is scanned 
ALL                 A full table scan

EXPLAIN Extra

 
Using index              The result is created straight from the index 
Using where              Not all rows are used in the result 
Distinct                 Only a single row is read per row combination 
Not exists               A LEFT JOIN missing rows optimization is used 
Using filesort           An extra row sorting step is done 
Using temporary          A temporary table is used 
Range checked for each   The read type is optimized individually for each combination of rows record from the previous tables

Optimizer Hints

STRAIGHT_JOIN       Forces the optimizer to join the tables in the given order 
SQL_BIG_RESULTS     Together with GROUP BY or DISTINCT tells the server to use disk-based temp tables 
SQL_BUFFER_RESULTS  Tells the server to use a temp table, thus releasing locks early (for table-locks) 
USE INDEX           Hints to the optimizer to use the given index 
FORCE INDEX         Forces the optimizer to use the index (if possible) 
IGNORE INDEX        Forces the optimizer not the use the index 

http://www.slideshare.net/osscube/mysql-performance-tuning-top-10-tips