List:General Discussion« Previous MessageNext Message »
From:walter harms Date:August 31 2011 8:52am
Subject:different desc for same statement
View as plain text  
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 |
+----+--------------------+-------+--------+-------------------+-----------+---------+----------------+------+--------------------------+
Thread
different desc for same statementwalter harms31 Aug
  • Re: different desc for same statementJohan De Meersman31 Aug
    • Re: different desc for same statementwalter harms31 Aug