In the last episode (Jan 15), Daevid Vincent said:
> From: Peter Brawley [mailto:peter.braw...@earthlink.net]
> > 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
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
