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]
