Can you show us the output of SHOW CREATE TABLE for the tables in your query? Looks like you just need some indexing!
Regards John John Daisley MySQL & Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -----Original Message----- From: Jia Chen [mailto:chen.1...@gmail.com] Sent: 28 August 2009 17:17 To: Dan Nelson; mysql@lists.mysql.com Subject: Re: Very Slow Query 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) Best, Jia Dan Nelson wrote: > 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? > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.71/2331 - Release Date: 08/28/09 06:26:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
