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 john@stripped
-----Original Message-----
From: Jia Chen [mailto:chen.1002@stripped]
Sent: 28 August 2009 17:17
To: Dan Nelson; mysql@stripped
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
>> # User@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=1
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
