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