List:General Discussion« Previous MessageNext Message »
From:Tristan Marly Date:April 13 2008 12:49pm
Subject:Re: Two MySql servers, but very different performances for a SELECT
JOIN
View as plain text  
(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
 
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