Skip navigation links

MySQL Forums :: Performance :: Workaround for left join against a subquery?


Advanced Search

Workaround for left join against a subquery?
Posted by: Stephen Gornick ()
Date: March 21, 2007 01:15PM

Bug #27199 ( http://bugs.mysql.com/bug.php?id=27199 ) describes how a left join against any subquery results in terrible performance (e.g., more than 100X slower than if using an inner join). Apparently this will not be fixed until 5.2 as MySQL responds:
"The query uses a derived table which is always materialized now. This problem will be fixed in future versions when derived tables will be handled as unnamed views."

I can create a table with the subquery results and then do a left join against the resulting table without incurring the performance hit, however that is a messy workaround and I would like to avoid creating additional tables.

Here's an example of what I want to do:

I have a parts table and an inventory table, and I want a query to tell me the next inventory id (First-in First-out) to use. Not all part ids in the inventory table will exist in the parts table so for any part ids that do not exist, use the substitute/default part id 1.

The query that works (and performs fine until I have a few thousand records) is:

select
parts.id,
min(inv2.id) as nextFIFOitemid
from test.parts
left join
( select
inventory.id,
coalesce(parts.id, 1) as partid
from test.inventory
left join test.parts
on (parts.id = inventory.partid)
) inv2
on (parts.id = inv2.partid)
group by parts.id;

Is there any trick using SQL that I can try that will let me avoid doing the left join to a subquery?

Options: ReplyQuote


Subject Views Written By Posted
Workaround for left join against a subquery? 7034 Stephen Gornick 03/21/2007 01:15PM
Re: Workaround for left join against a subquery? 2237 Stephen Gornick 03/21/2007 01:17PM
Re: Workaround for left join against a subquery? 1825 Björn Steinbrink 03/21/2007 09:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.