In the last episode (Aug 28), Jia Chen said: > One seemingly simple query that joins two tables takes a long time for me. > > This is my library. > > mysql> show table status from nber1999; > +-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > +-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | compusta1 | MyISAM | 10 | Dynamic | 4906 | 77 | > 379464 | 281474976710655 | 1024 | 0 | NULL | > 2009-08-27 23:56:47 | 2009-08-27 23:56:47 | NULL | latin1_swedish_ci | > NULL | | | > | pat1 | MyISAM | 10 | Dynamic | 2089903 | 96 | > 201936072 | 281474976710655 | 1024 | 0 | NULL | > 2009-08-27 23:55:48 | 2009-08-27 23:55:56 | NULL | latin1_swedish_ci | > NULL | | | > +-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > 5 rows in set (0.00 sec) > > And the relevant rows in my slow query log file is: > > # Time: 090828 10:36:17 > # u...@host: root[root] @ localhost [] > # Query_time: 478 Lock_time: 0 Rows_sent: 0 Rows_examined: 1251 > use nber1999; > create table nber1999.pat select a.*, b.assname, b.cname, b.cusip, > b.own, b.pname, b.sname > from nber1999.pat1 as a inner join nber1999.compusta1 as b > on a.assignee=b.assignee;
If you run just the "select ..." part, is it slow also? Do you have an index on pat1.assignee? What does an EXPLAIN on the select print? -- 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