List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:January 15 2010 8:51pm
Subject:RE: Better that `NOT IN`
View as plain text  
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.brawley@stripped] 
> Sent: Thursday, January 14, 2010 11:00 PM
> To: Junior Ortis
> Cc: mysql@stripped
> 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
> >
> >   
> 

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