From: walter harms
Date: August 31 2011 8:52am
Subject: different desc for same statement
List-Archive: http://lists.mysql.com/mysql/225619
Message-Id: <4E5DF64A.7080406@bfs.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

hi list,
i have a very strange effect.

I have two boxes with the same DB ( same version, same tables, same my.cnf, etc)
I was trying to optimise an sql statement and used desc to see what is going on
and found to my surprise two different results.

Can this be the result of the optimizer ? (the boxes have very different uptime)
re,
 wh

here the output of "desc statement;"

+----+--------------------+-------+--------+-------------------+---------+---------+----------------+------+--------------------------+
| id | select_type        | table | type   | possible_keys     | key     | key_len | ref            | rows | Extra                    |
+----+--------------------+-------+--------+-------------------+---------+---------+----------------+------+--------------------------+
|  1 | PRIMARY            | D     | ref    | PRIMARY,fk_mk_d   | fk_mk_d | 2       | const          | 1087 | Using where              |
|  1 | PRIMARY            | A     | ref    | PRIMARY,fk_mms_sb | PRIMARY | 4       |     D.kenn     |  198 | Using where              |
|  1 | PRIMARY            | B     | eq_ref | PRIMARY           | PRIMARY | 3       |     A.code     |    1 |                          |
|  2 | DEPENDENT SUBQUERY | C     | ref    | PRIMARY,fk_mms_sb | PRIMARY | 4       |     D.kenn     |  165 | Using where; Using index |
+----+--------------------+-------+--------+-------------------+---------+---------+----------------+------+--------------------------+


+----+--------------------+-------+--------+-------------------+-----------+---------+----------------+------+--------------------------+
| id | select_type        | table | type   | possible_keys     | key       | key_len | ref            | rows | Extra                    |
+----+--------------------+-------+--------+-------------------+-----------+---------+----------------+------+--------------------------+
|  1 | PRIMARY            | B     | ALL    | PRIMARY           | NULL      | NULL    | NULL           |   29 | Using where              |
|  1 | PRIMARY            | A     | ref    | PRIMARY,fk_mms_sb | fk_mms_sb | 3       |     B.code     | 2035 | Using where              |
|  1 | PRIMARY            | D     | eq_ref | PRIMARY,fk_mk_d   | PRIMARY   | 4       |     A.kenn     |    1 | Using where              |
|  2 | DEPENDENT SUBQUERY | C     | ref    | PRIMARY,fk_mms_sb | PRIMARY   | 4       |     D.kenn     |  574 | Using where; Using index |
+----+--------------------+-------+--------+-------------------+-----------+---------+----------------+------+--------------------------+