List:General Discussion« Previous MessageNext Message »
From:Jia Chen Date:August 28 2009 4:16pm
Subject:Re: Very Slow Query
View as plain text  
Thanks for reply!

Yes, it is very slow too  if I just execute the "select ..." part.

When I run
mysql> explain extended select a.*, b.assname, b.cname, b.cusip, b.own, 
b.pname, b.sname from nber1999.pat1 as a inner join nber1999.compusta1 
asb  on a.assignee=b.assignee;
I got
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | 
ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | 
NULL |    4906 |             |
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | 
NULL | 2089903 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
2 rows in set, 1 warning (0.00 sec)

Best,
Jia


Dan Nelson wrote:
> 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?
>  
>   

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