From: Wm Mussatto
Date: April 15 2008 4:05pm
Subject: Re: Two MySql servers,
      but very different performances for a SELECT JOIN
List-Archive: http://lists.mysql.com/mysql/212281
Message-Id: <3406.192.168.0.243.1208275500.squirrel@secure2.csz.com>
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit

On Tue, April 15, 2008 02:03, tmarly@stripped wrote:
> I'm still trying to decript the EXPLAIN result, and there is something I
> really
> don't understand:
>
> mysql> EXPLAIN SELECT STRAIGHT_JOIN media.* FROM country,content,media
> WHERE
> country.id='Germany' AND country.detail_tid=content.tid AND
> content.id=media.content_id;
>
> On the FAST server:
>
> +--+-------+-----+----------+----------+----+----------+----+
> |id|table  |type |possible_k|key       |k_le|ref       |rows|
> +--+-------+-----+----------+----------+----+----------+----+
> |1 |country|const|PRIMARY   |PRIMARY   |383 |const     |   1|
> |1 |content|ALL  |PRIMARY   |NULL      |NULL|NULL      | 137|
> |1 |media  |ref  |media_FI_2|media_FI_2|5   |content.id| 248|
> +--+-------+-----+----------+----------+----+----------+----+
>
> On the SLOW server:
> +--+-------+-----+----------+----------+----+----------+------+
> |id|table  |type |possible_k|key       |k_le|ref       | rows |
> +--+-------+-----+----------+----------+----+----------+------+
> |1 |country|ref  |PRIMARY   |PRIMARY   |383 |const     |     1|
> |1 |content|ALL  |PRIMARY   |NULL      |NULL|NULL      |   137|
> |1 |media  |ALL  |media_FI_2|NULL      |NULL|NULL      |125649|
> +--+-------+-----+----------+----------+----+----------+------+
>
> On the 3rd row, on the fast server, type=ref, and ref points toward the
> content.id column.
>
> And on the slow server, type=ALL, and ref points to NULL. Since my MySql
> schema
> defines explicitly the foreign key, should'n I have 'content.id' instead
> of
> 'NULL' ?
>
> What is the difference between 'possible_key' and 'key' columns ? why
> key=NULL
> on my slow server ?
>
>  Tristan
>
> --
> Tristan Marly
> http://www.linkedin.com/in/tristanmarly
>
Possible key refers to the keys that the server thinks might be usable. 
key refers to the one is chose.  On the slow server it decided that the
possible key would not work so it didn't use it.  You might try to force
the use of the key and see what happens.  Did you try organizing the slow
server's table.  Its possibly that there were enough records added/removed
that the statistics need to be updated.  Just a guess.

------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154