List:General Discussion« Previous MessageNext Message »
From:tmarly Date:April 11 2008 8:42am
Subject:Two MySql servers, but very different performances for a SELECT JOIN
View as plain text  
Hi,

I have 2 MySql server instances. One which is 5.0.27/Debian, another
5.0.32/Solaris.

Both instances have the same data in the database.

And I'm doing a select:
SELECT media.* FROM media,country,content WHERE country.id='Germany' AND
country.detail_tid=content.tid AND content.id=media.content_id;

This  request takes less than a half second on one server, and takes 70 seconds
on another server.

The EXPLAIN results are attached to this mail. Its shows that there are
interpreted very differently on each server.

I checked the database structure, wich is exactly identical on both instance.

Moreover, one could think that this is the optimizer which does not interpret
the joint request in the right order in the version 5.0.32 compared to 5.0.27,
but I'm pretty sure that this application had worked in the past (good
performance on 5.0.27).

Do you know how could I found some clues ?

 Tristan


-- 
Tristan Marly
06.16.84.57.43
http://www.linkedin.com/in/tristanmarly

mysql> explain select media.* from media,country,content 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                             |  140 | Using where |
|  1 | SIMPLE      | media   | ref   | media_FI_2    | media_FI_2 | 5       | integration.content.id           |  279 | 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                           | 180443 |             |
|  1 | SIMPLE      | content | eq_ref | PRIMARY       | PRIMARY | 4       | integration.media.content_id   |      1 | Using where |
+----+-------------+---------+--------+---------------+---------+---------+--------------------------------+--------+-------------+

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