Sunday, April 17, 2005

MySQL Explain example

EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| employee | ALL | NULL | NULL | NULL | NULL | 2 | where used |

So what are all these things?

-table shows us which table the output is about (for when you join many tables in the query)
-type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
-possible_keys Shows which possible indexes apply to this table
-key And which one is actually used
-key_len give us the length of the key used. The shorter that better.
-ref Tells us which column, or a constant, is used
-rows Number of rows mysql believes it must examine to get the data
extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

1 comment:

dora said...

Thanks a lot. it was a great help.