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
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
