List:General Discussion« Previous MessageNext Message »
From:John Date:August 28 2009 4:57pm
Subject:RE: Very Slow Query
View as plain text  
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

Thread
Very Slow QueryJia Chen28 Aug
  • Re: Very Slow QueryDan Nelson28 Aug
    • Re: Very Slow QueryJia Chen28 Aug
      • Re: Very Slow QueryDan Nelson28 Aug
        • Re: Very Slow QueryJia Chen28 Aug
      • RE: Very Slow QueryJohn28 Aug