Adding one subquery makes query a little slower, adding another makes it way slower

This is fast:

select ba.name,
penamt.value penamt,
#address_line4.value address_line4
from account a
join customer c on a.customer_id = c.id
join branch br on a.branch_id = br.id
join bank ba on br.bank_id = ba.id
join account_address aa on aa.account_id = a.id
join address ad on aa.address_i开发者_如何转开发d = ad.id
join state s on ad.state_id = s.id
join import i on a.import_id = i.id
join import_bundle ib on i.import_bundle_id = ib.id
join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id
#join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id
where i.active=1

And this is fast:

select ba.name,
#penamt.value penamt,
address_line4.value address_line4
from account a
join customer c on a.customer_id = c.id
join branch br on a.branch_id = br.id
join bank ba on br.bank_id = ba.id
join account_address aa on aa.account_id = a.id
join address ad on aa.address_id = ad.id
join state s on ad.state_id = s.id
join import i on a.import_id = i.id
join import_bundle ib on i.import_bundle_id = ib.id
#join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id
join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id
where i.active=1

But this is slow:

select ba.name,
penamt.value penamt,
address_line4.value address_line4
from account a
join customer c on a.customer_id = c.id
join branch br on a.branch_id = br.id
join bank ba on br.bank_id = ba.id
join account_address aa on aa.account_id = a.id
join address ad on aa.address_id = ad.id
join state s on ad.state_id = s.id
join import i on a.import_id = i.id
join import_bundle ib on i.import_bundle_id = ib.id
join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id
join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id
where i.active=1

Why is it fast when I include just one of the two subqueries but slow when I include both? I would think it should be twice as slow when I include both, but it takes a really long time. On on MySQL.

Here's an EXPLAIN:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    19584   
1   PRIMARY a   eq_ref  PRIMARY,fk_account_branch_id,fk_account_customer_id,fk_account_import_id    PRIMARY 8   penamt.account_id   1   
1   PRIMARY br  eq_ref  PRIMARY,fk_branch_bank_id   PRIMARY 8   mcif.a.branch_id    1   
1   PRIMARY i   eq_ref  PRIMARY,import_bundle_id    PRIMARY 8   mcif.a.import_id    1   "Using where"
1   PRIMARY ba  eq_ref  PRIMARY PRIMARY 8   mcif.br.bank_id 1   
1   PRIMARY c   eq_ref  PRIMARY PRIMARY 8   mcif.a.customer_id  1   "Using index"
1   PRIMARY ib  eq_ref  PRIMARY PRIMARY 8   mcif.i.import_bundle_id 1   "Using index"
1   PRIMARY aa  ref fk_account_address_account_id,fk_account_address_address_id fk_account_address_account_id   8   mcif.a.id   1   "Using where"
1   PRIMARY <derived3>  ALL NULL    NULL    NULL    NULL    368367  "Using where; Using join buffer"
1   PRIMARY ad  eq_ref  PRIMARY,fk_account_state_id PRIMARY 8   mcif.aa.address_id  1   
1   PRIMARY s   eq_ref  PRIMARY PRIMARY 8   mcif.ad.state_id    1   "Using index"
3   DERIVED unused  ref heading_label   heading_label   257     469722  "Using where"
2   DERIVED unused  ref heading_label   heading_label   257     15632   "Using where"

Doesn't answer your exact question, but if you change your query like this to get rid of the sub-queries altogether, I'd bet it would be significantly faster:

select ba.name,
penamt.value penamt,
address_line4.value address_line4
from account a
join customer c on a.customer_id = c.id
join branch br on a.branch_id = br.id
join bank ba on br.bank_id = ba.id
join account_address aa on aa.account_id = a.id
join address ad on aa.address_id = ad.id
join state s on ad.state_id = s.id
join import i on a.import_id = i.id
join import_bundle ib on i.import_bundle_id = ib.id
left join unused penamt on penamt.account_id = a.id and penamt.heading_label = 'PENAMT'
left join unused address_line4 on address_line4.account_id = a.id and address_line4.heading_label = 'Address Line 4'
where i.active=1

It appears you are looking for exception data... those missing some elements where your last two queries are against an "unused" table looking individually for a PEMAMT or Address Line 4 in the "heading_label" field. Additionally, you are not getting other information beyond that of the bank branch of the custoemr's account. So why add those tables unless you will be getting other data once your query works... That being said, I would prequery on your "exceptional" criteria up front, THEN join to the account table. Using the "STRAIGHT_JOIN" clause at the top tells the system to do it in the order listed. So, the prequery should be done first and qualify only those accounts needing review / missing data. THEN get the rest... Additionally, during the prequery, if I GROUP by the max... and getting the PENAMT and ADDRESS_LINE_4 in that query, I don't need to do it again in the later joins... Its qualified ONCE up front.

Here's my query for it.

select STRAIGHT_JOIN
      ba.name, 
      PreQuery.penamt, 
      PreQuery.address_line4
   from 
      ( SELECT account_id,
               MAX( CASE WHEN heading_label = 'PENAMT' 
                  THEN heading_label END ) penamt,
               MAX( CASE WHEN heading_label = 'Address Line 4' 
                  THEN heading_label END ) Address_line4
         from 
            unused
         where heading_label = 'PENAMT'
            OR heading_label = 'Address Line 4'
         group by 
            unused.account_id ) PreQuery
      join account a 
         ON PreQuery.account_id = a.id
      join customer c 
         on a.customer_id = c.id 
      join branch br 
         on a.branch_id = br.id 
      join bank ba 
         on br.bank_id = ba.id 
      join account_address aa 
         on a.id = aa.account_id
      join address ad 
         on aa.address_id = ad.id 
      join state s 
         on ad.state_id = s.id 
      join import i 
         on a.import_id = i.id 
      join import_bundle ib 
         on i.import_bundle_id = ib.id 
   where 
      i.active = 1