Sunday 9 June 2013

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:
SIMPLESimple SELECT (no UNION / subqueries)
PRIMARYOutermost SELECT
UNIONSecond or later SELECT statement in a UNION
DEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer query
UNION RESULTResult of a UNION.
SUBQUERYFirst SELECT in subquery
DEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer query
DERIVEDDerived 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