my query is a bit slow....

[windandwaves]

Hi Gurus

I have the following query:

DELETE ITEM.*
FROM ITEM
LEFT JOIN EXTRA_DETAILS ON
ITEM.item_code = EXTRA_DETAILS.object_code
WHERE EXTRA_DETAILS.field_value IS NULL;

ITEM has about 10,000 records in it and EXTRA_DETAILS has about 30,000
items in it. There are about 2,000 matches.

What I am trying to achieve is to delete all the records from ITEM that
do not have a match in EXTRA_DETAILS

For some reason the query takes like about 60 seconds to process.

I have added an index to EXTRA_DETAILS.object_code (int(12) and
ITEM.item_code is the primary index of ITEM (also int(12).

Any recommendations greatly appreciated.

Thanks a million

Nicolaas

[Peter]

How's the performance when you use a subquery for example...

DELETE FROM Item WHERE item_code IN (SELECT object_code FROM
EXTRA_DETAILS WHERE field_value IS NULL);
windandwaves wrote:

Hi Gurus

I have the following query:

DELETE ITEM.*
FROM ITEM
LEFT JOIN EXTRA_DETAILS ON
ITEM.item_code = EXTRA_DETAILS.object_code
WHERE EXTRA_DETAILS.field_value IS NULL;

ITEM has about 10,000 records in it and EXTRA_DETAILS has about 30,000
items in it. There are about 2,000 matches.

What I am trying to achieve is to delete all the records from ITEM that
do not have a match in EXTRA_DETAILS

For some reason the query takes like about 60 seconds to process.

I have added an index to EXTRA_DETAILS.object_code (int(12) and
ITEM.item_code is the primary index of ITEM (also int(12).

Any recommendations greatly appreciated.

Thanks a million

Nicolaas

[windandwaves]

Peter wrote:

How's the performance when you use a subquery for example...

DELETE FROM Item WHERE item_code IN (SELECT object_code FROM
EXTRA_DETAILS WHERE field_value IS NULL);

Hi Peter

I will give that a go. Thank you