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
# u...@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=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
