Leaderboard

Here's the structure of how your queries are run: foreach "SELECT * FROM production_data ORDER BY enterprise, job_number, TRIM(line_item) ASC" { $status_qc = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <10> ORDER BY submit_time DESC LIMIT 1"; $status_thermoforming = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <6> ORDER BY submit_time DESC LIMIT 1"; $status_vinylpaint = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <5> ORDER BY submit_time DESC LIMIT 1"; $status_finalassm = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <7> ORDER BY submit_time DESC LIMIT 1"; $status_crateship = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <8> ORDER BY submit_time DESC LIMIT 1"; } There are two complications in here: the fact that your departments are dynamic but your code cares about five specific ones, and that you want the most recent record for an order per department. This means that while all six of these queries could be combined into one, you'd end up with one query that uses 10 joins. That sucks. So I think I would settle for two queries: getting everything you want from production_data, but before that getting the most recent status per department for all orders. With some quick preprocessing on that you could look up each status very quickly. SELECT p1.order_id, p1.dept_code, p1.status_id FROM production_status p1 LEFT JOIN production_status p2 ON -- find similar records p1.order_id = p2.order_id AND -- ...for the same order p1.dept_code = p2.dept_code AND -- ...and the same department p2.submit_time > p1.submit_time -- ...and that come after the row that p1 found WHERE p1.dept_code IN (5, 6, 7, 8, 10) AND -- limit to department we care about p2.id IS NULL -- filter to keep only the p1 rows that didn't have later p2 rows $statuses = []; // foreach $row from that query { if (!isset($statuses[$row["order_id"]])) { $statuses[$row["order_id"]] = []; } $statuses[$row["order_id"]][$row["dept_code"]] = $row["status_id"]; // }