How many rows were added to rmpdata1 table? If it is 13.4 million rows then
it is going to take several minutes to join this many rows from the 2 tables.
Is there a 1:1 relationship between the two tables or a 1:Many? If there is
a 1:1 then I'd recommend joining the two tables into 1 table so you don't
have to join them in the first place.
The only other thing I can suggest is to change the type of index on the
tables being joined to see if that makes a speed difference. For example,
if you are using BTREE then switch to HASH or vice versa. See
http://dev.mysql.com/doc/refman/5.0/en/create-index.html for more info.
Mike
At 10:05 AM 9/5/2009, Jia Chen wrote:
>Hi there,
>
>One simple query took more than 10 minutes. Here is how relevant rows in
>the slow query log looks like:
>
># Time: 090905 10:49:57
># User@Host: root[root] @ localhost []
># Query_time: 649 Lock_time: 0 Rows_sent: 0 Rows_examined: 26758561
>use world;
>create table rmpdata1 select ri.*,
>mv.MV, coalesce(ri.code,mv.code) as ccode,
>coalesce(ri.ndate,mv.ndate) as cndate
>from RItime as ri left outer join
>MVtime as mv
>on (ri.code=mv.code and
>ri.ndate=mv.ndate);
>
>When I "explain" only the select clause, I get
>------------+----------+-------+
>| id | select_type | table | type | possible_keys | key | key_len |
>ref | rows | Extra |
>+----+-------------+-------+--------+---------------+---------+---------+------------------------------+----------+-------+
>| 1 | SIMPLE | ri | ALL | NULL | NULL | NULL
>| NULL | 13419851 | |
>| 1 | SIMPLE | mv | eq_ref | PRIMARY | PRIMARY | 11
>| world.ri.code,world.ri.ndate | 1 | |
>+----+-------------+-------+--------+---------------+---------+---------+------------------------------+----------+-------+
>2 rows in set (0.00 sec)
>
>I use "show table status from world;" to get information about two tables,
>RItime and MVtime, in the join clause:
> Name: RItime
> Engine: MyISAM
> Version: 10
> Row_format: Dynamic
> Rows: 13419851
>Avg_row_length: 31
> Data_length: 427721848
>Max_data_length: 281474976710655
> Index_length: 347497472
> Data_free: 0
>Auto_increment: NULL
> Create_time: 2009-09-03 10:17:57
> Update_time: 2009-09-03 12:04:02
> Check_time: NULL
> Collation: latin1_swedish_ci
> Checksum: NULL
>Create_options:
> Comment:
>*************************** 2. row ***************************
> Name: MVtime
> Engine: MyISAM
> Version: 10
> Row_format: Dynamic
> Rows: 13562373
>Avg_row_length: 31
> Data_length: 430220056
>Max_data_length: 281474976710655
> Index_length: 350996480
> Data_free: 0
>Auto_increment: NULL
> Create_time: 2009-09-03 13:31:33
> Update_time: 2009-09-03 13:43:51
> Check_time: NULL
> Collation: latin1_swedish_ci
> Checksum: NULL
>Create_options:
> Comment:
>
>I also describe these two tables:
>mysql> desc RItime;
>+-------+------------+------+-----+------------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+------------+------+-----+------------+-------+
>| code | varchar(6) | NO | PRI | | |
>| ndate | date | NO | PRI | 0000-00-00 | |
>| ri | double | YES | | NULL | |
>| time | date | YES | | NULL | |
>| bdate | date | YES | | NULL | |
>+-------+------------+------+-----+------------+-------+
>5 rows in set (0.00 sec)
>
>mysql> desc MVtime;
>+-------+------------+------+-----+------------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+------------+------+-----+------------+-------+
>| code | varchar(6) | NO | PRI | | |
>| ndate | date | NO | PRI | 0000-00-00 | |
>| MV | double | YES | | NULL | |
>| time | date | YES | | NULL | |
>| bdate | date | YES | | NULL | |
>+-------+------------+------+-----+------------+-------+
>5 rows in set (0.00 sec)
>
>Could you give me some hint on how to improve the speed of this query?
>Thanks.
>
>Best,
>Jia
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
