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.
>
>
