(cf. in attachment my notes. I attached there to avoid unwanted carriage
returns)
--
First, thanks for all your suggestions and for beeing so reactive.
@Martin: the explain result was in attachment, but you will have more results
in this current mail.
@Rob: you are right, the 'show index' shows strange things, cf. below.
@Rodolphe: indeed the STRAIGHT_JOIN has been very helpfull, cf. below.
@Brent: thanks for this very precise and technical answer.
So now, here is where I am:
==================================================================
1st comparison: using JOIN clause, starting with the 'media' table
==================================================================
mysql> EXPLAIN SELECT media.* FROM media JOIN content ON
content.id=media.content_id JOIN country ON country.detail_tid=content.tid AND
country.id='Germany';
On the FAST server:
+----+-------------+---------+-------+---------------+------------+---------+----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+----------------------------------+------+-------------+
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 383 |
const | 1 | |
| 1 | SIMPLE | content | ALL | PRIMARY | NULL | NULL |
NULL | 137 | Using where |
| 1 | SIMPLE | media | ref | media_FI_2 | media_FI_2 | 5 |
integration.content.id | 248 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+----------------------------------+------+-------------+
On the SLOW server:
+----+-------------+---------+--------+---------------+---------+---------+--------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------+--------+---------------+---------+---------+--------------------------------+--------+-------------+
| 1 | SIMPLE | country | ref | PRIMARY | PRIMARY | 383 |
const | 1 | Using where |
| 1 | SIMPLE | media | ALL | media_FI_2 | NULL | NULL |
NULL | 167531 | |
| 1 | SIMPLE | content | eq_ref | PRIMARY | PRIMARY | 4 |
integration.media.content_id | 1 | Using where |
+----+-------------+---------+--------+---------------+---------+---------+--------------------------------+--------+-------------+
=> The optimizer still joints in different order (country/content/media on the
fast, country/media/content on the slow)
==================================================================
2nd comparison: using JOIN clause, starting with the 'country' table
==================================================================
(as interestingly suggested by Brent)
mysql> EXPLAIN SELECT media.* FROM country JOIN content ON
country.detail_tid=content.tid JOIN media ON content.id=media.content_id WHERE
country.id='Germany';
=> No changements, exactly the same results as the 1st comparison.
==================================================================
3rd comparison: using STRAIGHT_JOIN
==================================================================
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 | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+----------------------------------+------+-------------+
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 383 |
const | 1 | |
| 1 | SIMPLE | content | ALL | PRIMARY | NULL | NULL |
NULL | 137 | Using where |
| 1 | SIMPLE | media | ref | media_FI_2 | media_FI_2 | 5 |
integration.content.id | 248 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+----------------------------------+------+-------------+
On the SLOW server:
+----+-------------+---------+------+---------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------+------+---------------+---------+---------+-------+--------+-------------+
| 1 | SIMPLE | country | ref | PRIMARY | PRIMARY | 383 | const
| 1 | Using where |
| 1 | SIMPLE | content | ALL | PRIMARY | NULL | NULL | NULL
| 137 | Using where |
| 1 | SIMPLE | media | ALL | media_FI_2 | NULL | NULL | NULL
| 125649 | Using where |
+----+-------------+---------+------+---------------+---------+---------+-------+--------+-------------+
=> So now that's better, the the join order is the same: the 2 first lines are
identical. The problem
occurs with the last line (table 'media').
1st question: I'm not sure if this is ok that type='ref' for the 1st, and
type='ALL' for the 2nd server.
==================================================================
Row count and show index
==================================================================
Since the pb since to be with the 'media' table, here are some statistics:
mysql> SELECT COUNT(*) FROM media;
FAST and SLOW server: same result: 22.000 records.
So my 2nd question is: why on the slow server have I 125.649 rows examined ?
mysql> ANALYZE TABLE media;
mysql> SHOW INDEX FROM media;
On the FAST server:
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| media | 0 | PRIMARY | 1 | id | A |
156879 | NULL | NULL | | BTREE | |
| media | 1 | media_FI_1 | 1 | place_id | A |
5602 | NULL | NULL | YES | BTREE | |
| media | 1 | media_FI_2 | 1 | content_id | A |
632 | NULL | NULL | YES | BTREE | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
On the SLOW server:
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| media | 0 | PRIMARY | 1 | id | A |
167531 | NULL | NULL | | BTREE | |
| media | 1 | media_FI_1 | 1 | place_id | A |
1882 | NULL | NULL | YES | BTREE | |
| media | 1 | media_FI_2 | 1 | content_id | A |
1 | NULL | NULL | YES | BTREE | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
=> Very different cardinality, and the highest cardinality is on the fast
server (?)
Do you have any clue ?
Tristan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
