Re: Two MySql servers, but very different performances for a SELECT JOIN

[tmarly]

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