MySQL select of select ?

Question

Hi all,

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.

Any suggestion or pointers would be greatly appreciated!

Thanks, Doug

Answer 1

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

Answer 2

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.