my query is a bit slow....

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

Re: my query is a bit slow....

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

Re: my query is a bit slow....

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