List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 28 2009 3:54pm
Subject:Re: Very Slow Query
View as plain text  
In the last episode (Aug 28), Jia Chen said:
> One seemingly simple query that joins two tables takes a long time for me.
> 
> This is my library.
> 
> mysql> show table status from nber1999;
>
> +-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
> | Name      | Engine | Version | Row_format | Rows     | Avg_row_length  |
> Data_length | Max_data_length  | Index_length | Data_free |  Auto_increment | Create_time 
>        | Update_time         | Check_time  | Collation         | Checksum | Create_options
> | Comment |
>
> +-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
> | compusta1 | MyISAM |      10 | Dynamic    |     4906 |             77  |     
> 379464 |  281474976710655 |         1024 |         0 |            NULL | 2009-08-27
> 23:56:47 | 2009-08-27 23:56:47 | NULL       |  latin1_swedish_ci |     NULL |             
>   |         |
> | pat1      | MyISAM |      10 | Dynamic    |  2089903 |             96  |  
> 201936072 |  281474976710655 |         1024 |         0 |            NULL | 2009-08-27
> 23:55:48 | 2009-08-27 23:55:56 | NULL       |  latin1_swedish_ci |     NULL |             
>   |         |
>
> +-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
> 5 rows in set (0.00 sec)
> 
> And the relevant rows in my slow query log file is:
> 
> # Time: 090828 10:36:17
> # User@Host: root[root] @ localhost []
> # Query_time: 478  Lock_time: 0  Rows_sent: 0  Rows_examined: 1251
> use nber1999;
> create table nber1999.pat select a.*, b.assname, b.cname, b.cusip, 
> b.own, b.pname, b.sname
>                  from nber1999.pat1 as a inner join nber1999.compusta1 as b
>                 on a.assignee=b.assignee;

If you run just the "select ..." part, is it slow also?  Do you have an
index on pat1.assignee?  What does an EXPLAIN on the select print?
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
Very Slow QueryJia Chen28 Aug
  • Re: Very Slow QueryDan Nelson28 Aug
    • Re: Very Slow QueryJia Chen28 Aug
      • Re: Very Slow QueryDan Nelson28 Aug
        • Re: Very Slow QueryJia Chen28 Aug
      • RE: Very Slow QueryJohn28 Aug