On 1/15/10 12:01 AM, "Junior Ortis" <jror...@gmail.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
