MySql explain

+----------+------+---------------+------+---------+------+------+------------+
| 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? http://dev.mysql.com/doc/refman/4.1/en/explain-output.html

-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"

mysql> explain extended select mail from users where uid in (168, 170);
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `mio1`.`users`.`mail` AS `mail` from `mio1`.`users` where (`mio1`.`users`.`uid` in (168,170))
1 row in set (0.00 sec)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s