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.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
