List:General Discussion« Previous MessageNext Message »
From:Jia Chen Date:August 28 2009 6:51pm
Subject:Re: Very Slow Query
View as plain text  
Hi Dan and John,

Thanks for your time!

You guys are right. I did not index any columns when I created these 
tables. After I indexed assignee columns in both tables, the select 
clause runs in seconds.

Best,
Jia

Dan Nelson wrote:
> In the last episode (Aug 28), Jia Chen said:
>   
>> 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)
>>     
>
> Ouch.  Add an index on pat1.assignee.  Mysql currently has to scan your
> entire pat1 table for every row in compusta1 to find matching rows.  In
> general, you want an index on any fields used in a WHERE clause.
>
>   

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