From: Jia Chen (chen.1002gmail.com)
Date: Fri Aug 28 2009 - 13:51:17 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]