List:General Discussion« Previous MessageNext Message »
From:fsb Date:January 15 2010 3:36pm
Subject:Re: Better that `NOT IN`
View as plain text  
On 1/15/10 12:01 AM, "Junior Ortis" <jrortis@stripped> wrote:

> Hi guys i have a problem, 3 big tables: item_instance about 15KK rows,
> character_inventory 15KK rows, guild_bank_item 2KK rows.
> 
> And i need i clean on item_instance how this query:
> 
> DELETE FROM `item_instance` WHERE guid NOT IN(SELECT item FROM
> `character_inventory`) AND guid NOT IN(SELECT item_guid FROM
> `guild_bank_item`) AND
> guid NOT IN(SELECT item_guid FROM `mail_items`) and guid NOT IN(SELECT
> itemguid FROM `auctionhouse`);
> 
> Well atm is running about 13 hours, State = Sending Data.
> 
> I will be a better option ?

would something like this work?

DELETE FROM item_instance i
LEFT JOIN character_inventory c ON c.item=i.guid
LEFT JOIN guild_bank_item g ON g.item_guid=i.guid
LEFT JOIN mail_items m ON m.item_guid=i.guid
LEFT JOIN auctionhouse a ON a.itemguid=i.guid
WHERE c.item IS NULL
AND g.item_guid IS NULL
AND m.item_guid IS NULL
AND a.itemguid IS NULL


Thread
Better that `NOT IN`Junior Ortis15 Jan
  • Re: Better that `NOT IN`Peter Brawley15 Jan
    • RE: Better that `NOT IN`Daevid Vincent15 Jan
      • Re: Better that `NOT IN`Dan Nelson15 Jan
  • Re: Better that `NOT IN`fsb15 Jan