title: MySql query taking a lot longer than it should
body: <p>For some reason this query takes up to 5 minutes to execute. I've expanded the join buffer to 1G and I did an Explain on this query (results are <a href="https://pastebin.com/Sp3JsaKA" rel="nofollow noreferrer">here</a>) Nothing seems to be indicating why this would take so much time. During the query all 8 cores of the CPU spike to near 100% usage.</p>
  
  <p>The engine is InnoDB.</p>
  
  <p>All tables have a primary key index.</p>
  
  <pre><code>SELECT Concat(Concat(cust.first_name, ' '), cust.last_name)    AS customerName, 
         TYPE.code                                               AS transType, 
         ty1.nsfamount, 
         np.sumrebateamount, 
         trans.note_id                                           AS note_id, 
         trans.createdate                                        AS createdatestr, 
         n.totalamount, 
         n.currentfloat, 
         ( ( n.costofborrowing * 100 ) / n.amounttolent )        AS fees, 
         n.amounttolent, 
         ( 0 - ( trans.cashamount + trans.chequeamount 
                 + trans.debitamount 
                 + trans.preauthorizedamount ) )                 AS paidamount, 
         sumpenaltyamount 
  FROM   (SELECT * 
          FROM   loan_transaction trans1 
          WHERE  trans1.cashamount &lt; 0 
                  OR trans1.chequeamount &lt; 0 
                  OR trans1.debitamount &lt; 0 
                  OR trans1.preauthorizedamount &lt; 0) trans 
         inner join customer cust 
                 ON trans.customer_id = cust.customer_id 
         inner join (SELECT * 
                     FROM   lookuptransactiontypes ty 
                     WHERE  ty.code &lt;&gt; 'REB' 
                            AND ty.code &lt;&gt; 'PN') TYPE 
                 ON trans.transactiontype = TYPE.transactiontypesid 
         inner join note n 
                 ON trans.note_id = n.note_id 
         inner join (SELECT note_id, 
                            SUM(rebateamount) AS sumrebateamount 
                     FROM   note_payment np1 
                     GROUP  BY np1.note_id) np 
                 ON trans.note_id = np.note_id 
         left join (SELECT note_id, 
                           transactiontype, 
                           ( SUM(chequeamount) + SUM(cashamount) 
                             + SUM(debitamount) + SUM(preauthorizedamount) )AS 
                           NSFamount 
                    FROM   (SELECT * 
                            FROM   loan_transaction trans4 
                            WHERE  trans4.cashamount &gt; 0 
                                    OR trans4.chequeamount &gt; 0 
                                    OR trans4.debitamount &gt; 0 
                                    OR trans4.preauthorizedamount &gt; 0)trans5 
                           inner join (SELECT transactiontypesid 
                                       FROM   lookuptransactiontypes ty2 
                                       WHERE  ty2.code = 'NSF')type2 
                                   ON 
                           trans5.transactiontype = type2.transactiontypesid 
                    GROUP  BY trans5.note_id) ty1 
                ON ty1.note_id = trans.refnum 
         left join (SELECT note_id                           AS noteid, 
                           ( SUM(tp.cashamount) + SUM(tp.chequeamount) 
                             + SUM(tp.debitamount) 
                             + SUM(tp.preauthorizedamount) ) AS sumpenaltyamount 
                    FROM   loan_transaction tp 
                           inner join (SELECT transactiontypesid 
                                       FROM   lookuptransactiontypes lp 
                                       WHERE  lp.code = 'PN') lp 
                                   ON lp.transactiontypesid = tp.transactiontype 
                    GROUP  BY tp.note_id) p 
                ON p.noteid = trans.refnum 
  </code></pre>
  
  <p><strong>Latest explain</strong></p>
  
  <pre><code>id  select_type  table       type    possible_keys    key                               key_len  ref                    rows    Extra                            
  1   PRIMARY      &lt;derived3&gt;  ALL                                                                                        2241                                     
  1   PRIMARY      &lt;derived4&gt;  ALL                                                                                        191441  Using join buffer                
  1   PRIMARY      n           eq_ref  PK_NOTE          PK_NOTE                           8        np.note_id             1                                        
  1   PRIMARY      &lt;derived2&gt;  ALL                                                                                        274992  Using where; Using join buffer   
  1   PRIMARY      cust        eq_ref  PRIMARY_97       PRIMARY_97                        8        trans.CUSTOMER_ID      1                                        
  1   PRIMARY      &lt;derived5&gt;  ALL                                                                                        2803                                     
  1   PRIMARY      &lt;derived8&gt;  ALL                                                                                        14755                                    
  8   DERIVED      &lt;derived9&gt;  ALL                                                                                        2       Using temporary; Using filesort  
  8   DERIVED      tp          ref     TRANSACTIONTYPE  TRANSACTIONTYPE                   9        lp.transactionTypesID  110     Using where                      
  9   DERIVED      lp          ALL                                                                                        2206    Using where                      
  5   DERIVED      &lt;derived7&gt;  ALL                                                                                        98      Using temporary; Using filesort  
  5   DERIVED      &lt;derived6&gt;  ALL                                                                                        314705  Using where; Using join buffer   
  7   DERIVED      ty2         ALL                                                                                        2206    Using where                      
  6   DERIVED      trans4      ALL                                                                                        664587  Using where                      
  4   DERIVED      np1         index                    note_payment_idx_id_rebateamount  16                              193366  Using index                      
  3   DERIVED      ty          ALL                                                                                        2206    Using where                      
  2   DERIVED      trans1      ALL                                                                                        664587  Using where
  </code></pre>