different desc for same statement

different desc for same statement

am 31.08.2011 10:52:26 von walter harms

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: different desc for same statement

am 31.08.2011 13:51:35 von Johan De Meersman

Exactly the same data, too? Different index leaf distribution might account for something like this, and it does look like you're retrieving different datasets.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: different desc for same statement

am 31.08.2011 14:07:46 von walter harms

Am 31.08.2011 13:51, schrieb Johan De Meersman:
> Exactly the same data, too? Different index leaf distribution might account for something
>like this, and it does look like you're retrieving different datasets.
>

same data

think of it as a backup.

re,
wh

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org