Re: Insane execution time for JOIN query
On 8/31/06, Harrison Fisk [EMAIL PROTECTED] wrote: Hello, On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. The problem is that your product_id is an INT in one table and a VARCHAR in the other. This will cause MySQL to have to do conversions of data types, which means it can't use indexes. Switch product_id to be an INT and both tables and it should solve your performance problems. Ouch. I reduced the DELETE... query time to 0.4 secs in total after changing the second table's product_id to an INT. Thanks alot, I sure wouldn't have seen this on my own after staring myself blind on this - you've saved me lots of work! I'm sure gonna be more careful with the data types next time! :-) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
On 8/31/06, Harrison Fisk ** [EMAIL PROTECTED] *** wrote: Hello, On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. The problem is that your product_id is an INT in one table and a VARCHAR in the other. This will cause MySQL to have to do conversions of data types, which means it can't use indexes. Switch product_id to be an INT and both tables and it should solve your performance problems. Ouch. I reduced the DELETE... query time to 0.4 secs in total after changing the second table's product_id to an INT. Thanks alot, I sure wouldn't have seen this on my own after staring myself blind on this - you've saved me lots of work! I'm sure gonna be more careful with the data types next time! :-) Having taken a weeklong MySQL course with Harry a number of years ago, I'm not surprised by his knowledge and acumen. It's a benefit to us all that he still frequents this mailing list. David Giragosian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insane execution time for JOIN query
Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the items table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the items table gets removed after the nightly update, and I'm working on a query which removes all rows in the products table that doesn't have any corresponding rows (matched with product_id) in the items table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as Sending data in the process list. Any ideas? products has ~113.500 records while items has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || | item_price | float| NO | | 0 || | item_stock | int(11) | NO | | 0 || | item_incoming| varchar(45) | NO | | || | item_updated | int(10) unsigned | NO | | 0 || | item_url | varchar(255) | NO | | || | supplier_id | int(10) unsigned | NO | | 0 || | item_internal_id | varchar(45) | NO | MUL | || | item_description | varchar(255) | NO | | || | item_weight | float| NO | | 0 || +--+--+--+-+-++ Best regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? - Original Message - From: Kim Christensen [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the items table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the items table gets removed after the nightly update, and I'm working on a query which removes all rows in the products table that doesn't have any corresponding rows (matched with product_id) in the items table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as Sending data in the process list. Any ideas? products has ~113.500 records while items has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || | item_price | float| NO | | 0 || | item_stock | int(11) | NO | | 0 || | item_incoming| varchar(45) | NO | | || | item_updated | int(10) unsigned | NO | | 0 || | item_url | varchar(255) | NO | | || | supplier_id | int(10) unsigned | NO | | 0 || | item_internal_id | varchar(45) | NO | MUL | || | item_description | varchar(255) | NO | | || | item_weight | float| NO | | 0 || +--+--+--+-+-++ Best regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
On 8/31/06, Brent Baisley [EMAIL PROTECTED] wrote: How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; ++-+--+--+---+--+-+--++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--++-+ | 1 | SIMPLE | products | ALL | | | | | 89447 | | | 1 | SIMPLE | items| ALL | PRICE_INFO| | | | 123223 | Using where; Not exists | ++-+--+--+---+--+-+--++-+ As you can see I have an index (UNIQUE) set that I use for an on INSERT... ON DUPLICATE method while updating the prices every morning, is this the messy part? Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? They are both MyISAM, with no other options changed from default. - Original Message - From: Kim Christensen [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the items table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the items table gets removed after the nightly update, and I'm working on a query which removes all rows in the products table that doesn't have any corresponding rows (matched with product_id) in the items table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as Sending data in the process list. Any ideas? products has ~113.500 records while items has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 | | | item_price | float| NO | | 0 | | | item_stock | int(11) | NO | | 0 | | | item_incoming| varchar(45) | NO | | | | | item_updated | int(10
Re: Insane execution time for JOIN query
hmmm, not sure why it's only scanning 89K records from the products table, I would think it would scan the whole table. It is scanning the entire items table, which I would think it wouldn't do. How about posting your SHOW INDEX FROM items result. Also, what are your memory settings? SHOW VARIABLES LIKE %buffer_size - Original Message - From: Kim Christensen [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 31, 2006 9:12 AM Subject: Re: Insane execution time for JOIN query On 8/31/06, Brent Baisley [EMAIL PROTECTED] wrote: How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; ++-+--+--+---+--+-+--++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--++-+ | 1 | SIMPLE | products | ALL | | | | | 89447 | | | 1 | SIMPLE | items| ALL | PRICE_INFO| | | | 123223 | Using where; Not exists | ++-+--+--+---+--+-+--++-+ As you can see I have an index (UNIQUE) set that I use for an on INSERT... ON DUPLICATE method while updating the prices every morning, is this the messy part? Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? They are both MyISAM, with no other options changed from default. - Original Message - From: Kim Christensen [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the items table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the items table gets removed after the nightly update, and I'm working on a query which removes all rows in the products table that doesn't have any corresponding rows (matched with product_id) in the items table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as Sending data in the process list. Any ideas? products has ~113.500 records while items has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra
Re: Insane execution time for JOIN query
On 8/31/06, Brent Baisley [EMAIL PROTECTED] wrote: hmmm, not sure why it's only scanning 89K records from the products table, I would think it would scan the whole table. It is scanning the entire items table, which I would think it wouldn't do. Well, the query in question actually did do some work, the current row count IS 89k. My bad. The speed is still an issue though! How about posting your SHOW INDEX FROM items result. +---+++--+--+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+--+---+-+--++--++-+ | items | 0 | PRIMARY| 1| item_id | A | 123223 | NULL || | BTREE | | | items | 0 | PRICE_INFO | 1| product_id | A | NULL| NULL || | BTREE | | | items | 0 | PRICE_INFO | 2| supplier_id | A | 123223 | NULL || | BTREE | | | items | 1 | retail_id | 1| item_internal_id | A | 123223 | NULL || | BTREE | | +---+++--+--+---+-+--++--++-+ Also, what are your memory settings? SHOW VARIABLES LIKE %buffer_size +-+--+ | Variable_name | Value| +-+--+ | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | preload_buffer_size | 32768| | read_buffer_size| 131072 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | +-+--+ The box has 1GB of physical RAM and 2GB in one swap partition. Thanks for the help so far! -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
Kim, The first thing I'd do is run a MyISAMChk on the table to see if the index is damaged. The second thing I'd do is run Optimize on the tables regularly because after a lot of rows have been deleted it leaves holes in the table which slows down table performance. Of course you can do an Explain on: select * from products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; to see if it is using the index. And finally turning it into a Subselect might speed it up. (Yes, subselects can run faster than table joins in some cases) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
Hello, On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. The problem is that your product_id is an INT in one table and a VARCHAR in the other. This will cause MySQL to have to do conversions of data types, which means it can't use indexes. Switch product_id to be an INT and both tables and it should solve your performance problems. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Table descriptions: PRODUCTS ++--+--+-+- ++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+- ++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | ITEMS: +--+--+--+-+- ++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+- ++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || Regards, Harrison -- Harrison C. Fisk, Senior Support Engineer MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/ packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
