Select of select doesn't complete in new MySQL version

I'm having problems with a SQL query, here's an generalization of what I'm trying to do:

select 
oh.a  as a, 
oh.b  as b, 
oi.c  as c,
(select h.d from history h where h.id = oh.id and h.d not in ('d1', 'd2') order by h.date limit 1) as d开发者_如何学运维
from order_header oh
join order_item oi on oh.order_id = oi.order_id
where oh.state in (0, 10, 20)

My problem is this type of query works fine in MySQL version 5.0.77, but it fails in MySQL version 5.1.47. And by problem I mean when the query runs MySQL pegs the CPU at 100% and it never completes. Even putting an explain in front of the select makes the query never return.

Here's how I would write this query:

select 
oh.a  as a, 
oh.b  as b, 
oi.c  as c,
h1.d as d
from order_header oh
join order_item oi on oh.order_id = oi.order_id
left outer join history h
 on h.id = oh.id and h.d not in ('d1', 'd2')
left outer join history h2
 on h2.id = oh.id and h2.d not in ('d1', 'd2') 
 and (h.date > h2.date or h.date = h2.date and h.id > h2.id) 
where oh.state in (0, 10, 20) and h2.id is null

I've found MySQL's profiling mode to be just as useful as EXPLAIN. You can enable it before running your query and then dump the timings for each step. It's a really handy way to optimize subqueries -- you may notice your subquery is executing for every row in a SELECT clause when it could execute a single time as a WHERE clause.