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) 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] >
-- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
