Very Slow Query
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 CommandArgument # 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; 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-mysqltuner/ and got MySQLTuner 1.0.0 - Major Hayden ma...@mhtx.net 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=arch...@jab.org
Re: Very Slow Query
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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=arch...@jab.org
Re: Very Slow Query
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. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Very Slow Query
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
Re: Very Slow Query
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=arch...@jab.org
Very slow query
Hello everbody, I have a table in my MySQL 5.0.22 Server with this columns. +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | DATE | datetime | NO | MUL | -00-00 00:00:00 | | | IMAGE| longblob | NO | | NULL| | | IMAGETYPE| varchar(8) | NO | MUL | NULL| | | KEYFILE | longblob | NO | | NULL| | | SEARCHENGINE | varchar(64) | NO | MUL | NULL| | | SEARCHTERM | varchar(256) | NO | MUL | NULL| | | SIZE | int(11) | NO | MUL | 0 | | | URL | varchar(512) | NO | | NULL| | | NAME | varchar(256) | NO | MUL | NULL| | +--+--+--+-+-+---+ When I insert several hundert rows with data (all columns, but without the keyfile column) and query the database with this string: SELECT date, imagetype, searchengine, searchterm, size, url, name FROM digiforensic where searchterm='...' it goes very fast (less than one second) and the right index for searchterm is used. This even works when I shutdown and restart the server. The problem is, that the keyfiles are calculated after the datasets are inserted into the database. After calculation they are inserted with an update of the corresponding row. After that the query of the datasets takes a lot of time - more than 2 minutes. I checked that the right index is used with the EXPLAIN expression. Did anybody know why this happens or what my error is? Thanks and Regards Michael begin:vcard fn:Michael Sutter n:Sutter;Michael org:Forschungszentrum Karlsruhe;Institute of Data Processing and Electronics adr:Hermann-von-Helmholtz-Platz 1;;Geb. 442;Eggenstein-Leopoldshafen;;76344;Germany email;internet:[EMAIL PROTECTED] tel;work:+49 7247 825676 tel;fax:+49 7247 823560 version:2.1 end:vcard smime.p7s Description: S/MIME Cryptographic Signature
Re: Very slow query
At 08:10 AM 7/21/2006, Michael Sutter wrote: Hello everbody, I have a table in my MySQL 5.0.22 Server with this columns. +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | DATE | datetime | NO | MUL | -00-00 00:00:00 | | | IMAGE| longblob | NO | | NULL| | | IMAGETYPE| varchar(8) | NO | MUL | NULL| | | KEYFILE | longblob | NO | | NULL| | | SEARCHENGINE | varchar(64) | NO | MUL | NULL| | | SEARCHTERM | varchar(256) | NO | MUL | NULL| | | SIZE | int(11) | NO | MUL | 0 | | | URL | varchar(512) | NO | | NULL| | | NAME | varchar(256) | NO | MUL | NULL| | +--+--+--+-+-+---+ When I insert several hundert rows with data (all columns, but without the keyfile column) and query the database with this string: SELECT date, imagetype, searchengine, searchterm, size, url, name FROM digiforensic where searchterm='...' it goes very fast (less than one second) and the right index for searchterm is used. This even works when I shutdown and restart the server. The problem is, that the keyfiles are calculated after the datasets are inserted into the database. After calculation they are inserted with an update of the corresponding row. After that the query of the datasets takes a lot of time - more than 2 minutes. I checked that the right index is used with the EXPLAIN expression. Did anybody know why this happens or what my error is? Try moving the LongBlob to a table by itself with a rcd_id column that matches the one in your other table. Then do a join on the two tables when you need to access KeyFile. The problem may be KeyFile is very large and that results in a lot of unecessary disk i/o. Mike Thanks and Regards Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Very slow query
The didn't show up on the list 2 days after posting it, so I'm posting it again. -- Forwarded message -- From: Mike Wexler [EMAIL PROTECTED] Date: Fri, 22 Oct 2004 14:39:49 -0700 Subject: Very slow query To: [EMAIL PROTECTED] The following query: CREATE TEMPORARY TABLE orders.recentClickOrders SELECT recentHeader.transNum, recentClickLog.adRunId, links.letterId, recentHeader.transactionTime FROM orders.recentHeader, banner.recentClickLog, mailing_list.links WHERE recentHeader.cookie=recentClickLog.cookie ANDrecentClickLog.adRunId=links.adRunId ANDrecentClickLog.cookie IS NOT NULL explain says: ++--++-+-++--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--++-+-++--+-+ | recentHeader | ALL | cookie | NULL|NULL | NULL | 9456 | | | recentClickLog | ref | adRunId,cookie | cookie | 256 | recentHeader.cookie|5 | Using where | | links | ref | adRunId| adRunId | 4 | recentClickLog.adRunId | 12 | describe orders.recentHeader; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | transNum| int(11) | | | 0 | | | cookie | varchar(128) | YES | MUL | NULL| | | transactionTime | datetime | | | -00-00 00:00:00 | | +-+--+--+-+-+---+ describe banner.recentClickLog; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | logKey| int(12) unsigned | | | 0 | | | timeStamp | timestamp(14)| YES | | NULL| | | adRunId | int(11) | | MUL | 0 | | | cookie| varchar(255) | YES | MUL | NULL| | | letterId | int(11) | | MUL | 0 | | +---+--+--+-+-+---+ describe mailing_list.links; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | linkId | int(12) unsigned | | PRI | NULL| auto_increment | | letterId | int(11) | | MUL | 0 || | adRunId | int(11) | | MUL | 0 || +--+--+--+-+-++ and show keys from mailing_list.links; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | links | 0 | PRIMARY |1 | linkId | A | 563158 | NULL | NULL | | BTREE | | | links | 1 | adRunId |1 | adRunId | A |NULL | NULL | NULL | | BTREE | | | links | 1 | letterId |1 | letterId| A |NULL | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ show keys from orders.recentHeader; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | recentHeader | 1 | cookie |1 | cookie | A |4728 | NULL | NULL | YES | BTREE | | +--++--+--+-+---+-+--++--++-+ show keys from banner.recentClickLog; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index
Re: Very slow query
Although you're not really asking a question, I presume that you want to know why the query is very slow and how you speed it up. The following query: CREATE TEMPORARY TABLE orders.recentClickOrders SELECT recentHeader.transNum, recentClickLog.adRunId, links.letterId, recentHeader.transactionTime FROM orders.recentHeader, banner.recentClickLog, mailing_list.links WHERE recentHeader.cookie=recentClickLog.cookie ANDrecentClickLog.adRunId=links.adRunId ANDrecentClickLog.cookie IS NOT NULL explain says: ++--++-+-+-- --+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--++-+-+-- --+--+-+ | recentHeader | ALL | cookie | NULL|NULL | NULL | 9456 | | | recentClickLog | ref | adRunId,cookie | cookie | 256 | recentHeader.cookie|5 | Using where | | links | ref | adRunId| adRunId | 4 | recentClickLog.adRunId | 12 | First of all, MySQL can use one index per table in a join. This means that it can use either adRunId or cookie from recentClickLog. You can create an index on adRunId and cookie. If either the first part(s) or all parts of the index are used MySQL can use this combined index to link to both other tables. describe orders.recentHeader; | cookie | varchar(128) | YES | MUL | NULL| | describe banner.recentClickLog; | cookie| varchar(255) | YES | MUL | NULL| | As you can see both definitions are not the same. MySQL will have to convert the values in order to compare them. Although the conversion seems very trivial in this case, it might prevent MySQL from using the index cookie from the recentHeader table! I would make both tables varchar(128) as it seems big enough to hold the cookie data in your case. show keys from mailing_list.links; +-+---+-+ | Column_name | Collation | Cardinality | +-+---+-+ | linkId | A | 563158 | | adRunId | A |NULL | | letterId| A |NULL | Did you run something like OPTIMIZE TABLE recently on this table? If the cardinality numbers (they are an estimate of how many unique values are present for this column) are reasonably accurate, MySQL can optimize the table order in the query a bit better. Finally, something that is discouraged by the manual, but sometimes it can improve the speed of queries quite a bit: Try and see if the query gets faster when you move the conditions from the where clause to the joins: CREATE TEMPORARY TABLE orders.recentClickOrders SELECT recentHeader.transNum, recentClickLog.adRunId, links.letterId, recentHeader.transactionTime FROM orders.recentHeader JOIN banner.recentClickLog ON recentHeader.cookie=recentClickLog.cookie AND recentClickLog.cookie IS NOT NULL JOIN mailing_list.links ON recentClickLog.adRunId=links.adRunId I won't promise you miracles, but sometimes it helps. Generally speaking, your efforts should focus on getting the predicted number of rows in the explain output as low as possible. This results often in faster queries despite discouraging remarks in the type and extra columns of the explain output. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
