List:General Discussion« Previous MessageNext Message »
From:Jia Chen Date:September 5 2009 3:05pm
Subject:How to optimize a slow query?
View as plain text  
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



Thread
How to optimize a slow query?Jia Chen5 Sep
  • Re: How to optimize a slow query?mos6 Sep
    • Re: How to optimize a slow query?Jia Chen6 Sep
      • Re: How to optimize a slow query?mos6 Sep
        • Re: How to optimize a slow query?Jia Chen6 Sep
          • Re: How to optimize a slow query?mos6 Sep
            • Re: How to optimize a slow query?Jia Chen7 Sep