Very Slow Query

am 28.08.2009 17:17:40 von chen jia

Hi all,

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:

/usr/sbin/mysqld, Version: 5.0.75-0ubuntu10.2-log ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# 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;

My operating system is ubuntu 9.04. I set configuration variables as
follows:
[mysqld]
key_buffer = 1024M
table_cache = 256
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 20M

[isamchk]
key_buffer = 16M

Can anyone give me some hint on how to speed this query up? Thanks.

I tried to tune mysql by using a script from
http://mediakey.dk/~cc/optimize-mysql-performance-with-mysql tuner/
and got

>> MySQLTuner 1.0.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.75-0ubuntu10.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
-------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 584M (Tables: 6)
[OK] Total fragmented tables: 0

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 30m 47s (131 q [0.071 qps], 42 conn, TX: 35K, RX: 7K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 1.0G global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.3G (34% of installed RAM)
[OK] Slow queries: 0% (1/131)
[OK] Highest usage of available connections: 2% (2/100)
[OK] Key buffer size / total MyISAM indexes: 1.0G/74.0K
[!!] Query cache efficiency: 0.0% (0 cached / 67 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 19% (21 on disk / 107 total)
[OK] Thread cache hit rate: 95% (2 created / 42 connections)
[OK] Table cache hit rate: 75% (24 open / 32 opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 100% (41 immediate / 41 locks)

-------- Recommendations
-----------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_limit (> 2M, or use smaller result sets)

Best,
Jia

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Very Slow Query

am 28.08.2009 17:54:36 von Dan Nelson

Re: Very Slow Query

am 28.08.2009 18:16:56 von chen jia

Re: Very Slow Query

am 28.08.2009 18:54:31 von Dan Nelson

RE: Very Slow Query

am 28.08.2009 18:57:01 von John

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@butterflysystems.co.uk

-----Original Message-----
From: Jia Chen [mailto:chen.1002@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
>> # 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=john.daisley@butterflysys tems.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=gcdmg-mysql-2@m.gmane.org

Re: Very Slow Query

am 28.08.2009 20:51:17 von chen jia

Hi Dan and John,

Thanks for your time!

You guys are right. I did not index any columns when I created these
tables. After I indexed assignee columns in both tables, the select
clause runs in seconds.

Best,
Jia

Dan Nelson wrote:
> In the last episode (Aug 28), Jia Chen said:
>
>> 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)
>>
>
> Ouch. Add an index on pat1.assignee. Mysql currently has to scan your
> entire pat1 table for every row in compusta1 to find matching rows. In
> general, you want an index on any fields used in a WHERE clause.
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org