List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 15 2010 10:23pm
Subject:Re: Better that `NOT IN`
View as plain text  
In the last episode (Jan 15), Daevid Vincent said:
> From: Peter Brawley [mailto:peter.brawley@stripped] 
> > Junior Ortis wrote:
> > > Hi guys i have a problem, 3 big tables: item_instance about 15K rows,
> > > character_inventory 15K rows, guild_bank_item 2K 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`);
>
> 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...) ?

mysql's subquery optimizer is still pretty bad.  It assumes that almost all
subqueries are dependant, and runs them for each outer record.  It really
should realize that those queries were independant of the outer query, and
cache the results (or hoist the query out of the loop).  Ideally the
performance should be identical to IN(list of constants).

> 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?

IN() using constants should be very efficient.

-- 
	Dan Nelson
	dnelson@stripped
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