From: Jia Chen (chen.1002gmail.com)
Date: Sat Sep 05 2009 - 10:05:26 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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
# UserHost: 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]