MySQL Forums
Forum List  »  Performance

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

Bug #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:

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

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

Options: ReplyQuote

Written By
Workaround for left join against a subquery?
March 21, 2007 01:15PM

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.