Thanks for your reply, Mike.
Yes, 13419851 rows were added to rmpdata1. However, 10 minutes seem to
be too long. I run the same join by using SQL procedure in a statistical
software called SAS on a similar machine. It only takes 1 minute and 3
seconds.
Yes, it is a 1:1 relationship between table RItime and MVtime. However,
I don't get your suggestion, "I'd recommend joining the two tables into
1 table so you don't have to join them in the first place." Could you
elaborate that?
I was using B-tree index. Switching to HASH does help. Now, it takes 4
min 50.17 sec to run the query.
I also turn on profiling by using
mysql> set profiling = 1;
Query OK, 0 rows affected (0.01 sec)
After the query finishes, I get
mysql> show profile;
+----------------------+------------+
| Status | Duration |
+----------------------+------------+
| starting | 0.000123 |
| checking permissions | 0.000010 |
| Opening tables | 0.000044 |
| System lock | 0.000007 |
| Table lock | 0.000011 |
| init | 0.000083 |
| creating table | 0.003428 |
| After create | 0.000124 |
| System lock | 0.000004 |
| Table lock | 0.000051 |
| optimizing | 0.000007 |
| statistics | 0.000033 |
| preparing | 0.000020 |
| executing | 0.000004 |
| Sending data | 290.153530 |
| end | 0.000008 |
| end | 0.000004 |
| query end | 0.000003 |
| freeing items | 0.000010 |
| closing tables | 0.000025 |
| logging slow query | 0.000001 |
| logging slow query | 0.013429 |
| cleaning up | 0.000004 |
+----------------------+------------+
23 rows in set (0.02 sec)
MySQL spends most of its time sending data. According to
http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html,
sending data means that "the thread is processing rows for a |SELECT|
<http://dev.mysql.com/doc/refman/5.0/en/select.html> statement and also
is sending data to the client." Is there more room to optimize this
query? Thanks again.
Best,
Jia
mos wrote:
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
