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=arch...@jab.org
