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
[email protected]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
