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?