List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:April 15 2008 4:05pm
Subject:Re: Two MySql servers,
but very different performances for a SELECT JOIN
View as plain text  
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

Thread
Two MySql servers, but very different performances for a SELECT JOINtmarly11 Apr
  • Re: Two MySql servers, but very different performances for a SELECT JOINRob Wultsch11 Apr
  • Re: Two MySql servers, but very different performances for a SELECTJOINTristan Marly13 Apr
    • Re: Two MySql servers, but very different performances for a SELECTJOINSebastian Mendel14 Apr
Re: Two MySql servers, but very different performances for a SELECT JOINtmarly15 Apr
  • Re: Two MySql servers,but very different performances for a SELECT JOINWm Mussatto15 Apr
    • Re: Two MySql servers, but very different performances for a SELECT JOINRob Wultsch15 Apr
  • Re: Two MySql servers, but very different performances for a SELECT JOINtmarly16 Apr
    • Re: Two MySql servers, but very different performances for a SELECT JOINRob Wultsch16 Apr
      • Re: Two MySql servers, but very different performances for a SELECT JOINtmarly16 Apr