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 |
+----+-------------+---------+--------+---------------+---------+---------+--------------------------------+--------+-------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
