Re: Workaround for left join against a subquery?

[Stephen Gornick]

Here is test data which will cause the query to perform poorly.

create schema test;
create table test.parts (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=MyISAM;

insert into test.parts values (NULL);
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;
INSERT INTO test.parts SELECT NULL FROM test.parts;


create table test.inventory (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
partid INT UNSIGNED NOT NULL
) ENGINE=MyISAM;

INSERT INTO test.inventory values (NULL, 2);
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;
INSERT INTO test.inventory SELECT NULL, 2 FROM test.inventory;

-- A part that does not exist in parts table.
INSERT INTO test.inventory values (NULL, 0);

-- Answers the question:
-- What is the next inventory item id to sell (FIFO).
-- Treat any part numbers that do not exist in parts table
-- as part id 1.
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
order by
parts.id
limit 5;