Peter, this only addresses a sub-select scenario, which doesn't surprise me it would be slow. How does IN() fare if you populate it with the ID's directly? For example: IN(1,3,6,8,19,45,54...) ?
In the example, on the web page, we could have run this as two separate queries. One for the 'inner' select to get an array of orderID and then shove those back into the 'outter' query using PHP's implode() or something. I suspect this would be significantly faster no? > -----Original Message----- > From: Peter Brawley [mailto:peter.braw...@earthlink.net] > Sent: Thursday, January 14, 2010 11:00 PM > To: Junior Ortis > Cc: mysql@lists.mysql.com > Subject: Re: Better that `NOT IN` > > For alternatives, have a look at "The unbearable slowness of IN()" at > http://www.artfulsoftware.com/queries.php. > > PB > > ----- > > Junior Ortis 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 ? > > > > Thanks all ! > > > > > > > -------------------------------------------------------------- > ---------- > > > > > > No virus found in this incoming message. > > Checked by AVG - www.avg.com > > Version: 8.5.432 / Virus Database: 270.14.139/2620 - > Release Date: 01/14/10 07:35:00 > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
