MySql query taking a lot longer than it should

Question

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 here) 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.

The engine is InnoDB.

All tables have a primary key index.

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 < 0 
                OR trans1.chequeamount < 0 
                OR trans1.debitamount < 0 
                OR trans1.preauthorizedamount < 0) trans 
       inner join customer cust 
               ON trans.customer_id = cust.customer_id 
       inner join (SELECT * 
                   FROM   lookuptransactiontypes ty 
                   WHERE  ty.code <> 'REB' 
                          AND ty.code <> '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 > 0 
                                  OR trans4.chequeamount > 0 
                                  OR trans4.debitamount > 0 
                                  OR trans4.preauthorizedamount > 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

Latest explain

id  select_type  table       type    possible_keys    key                               key_len  ref                    rows    Extra                            
1   PRIMARY      <derived3>  ALL                                                                                        2241                                     
1   PRIMARY      <derived4>  ALL                                                                                        191441  Using join buffer                
1   PRIMARY      n           eq_ref  PK_NOTE          PK_NOTE                           8        np.note_id             1                                        
1   PRIMARY      <derived2>  ALL                                                                                        274992  Using where; Using join buffer   
1   PRIMARY      cust        eq_ref  PRIMARY_97       PRIMARY_97                        8        trans.CUSTOMER_ID      1                                        
1   PRIMARY      <derived5>  ALL                                                                                        2803                                     
1   PRIMARY      <derived8>  ALL                                                                                        14755                                    
8   DERIVED      <derived9>  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      <derived7>  ALL                                                                                        98      Using temporary; Using filesort  
5   DERIVED      <derived6>  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

"Nothing seems to be indicating why this would take so much time" -> "Using temporary; Using filesort " on 207662 rows on table np1 are indicators off the most worse performance troubles within MySQL..relateds to this query `SELECT note_id, SUM(rebateamount) AS sumrebateamount FROM note_payment np1 GROUP BY np1.note_id` which requires the `index(note_id, rebateamount)... With other words you need to carefully check the required index(es) per delivered table — Raymond Nijland, Apr 23 '18 at 13:14
Just added the index and the Explain no longer has any operations on large amounts of rows using filesort (Explain data: https://pastebin.com/q12uHKUE) The query still takes just as long sadly. — Sara Brazille, Apr 23 '18 at 13:58
I´ve added the latest explain to the question.. yea it´s hard to improve further without knowing the table structures and indexes.. — Raymond Nijland, Apr 23 '18 at 14:14
Update: Removing the GROUP BY speeds up the query by orders of magnitude — Sara Brazille, Apr 23 '18 at 14:45
What version of MySQL? Newer versions do a better job at `JOIN ( SELECT ... )`; you need such optimizations! — Rick James, Apr 27 '18 at 18:04

score 1 · Answer 1 · answered Apr 23 '18 at 14:12

to be honest, there is a lot wrong with that query. You can easily simplify it following theses rules :

You can concat multiple columns at once ( Ex: CONCAT(column1, ' ', column2))
There is no need to do a subquery with an inner join on the same table ( or right in the first FROM ). Just put your FROM directly on the subquery's main table, and move your subquery's filters in the main query's WHERE
Not sure about that, but it seems all your logic is based on a per note_id basis. If that is indeed the case, move the GROUP BY note_id in your main query's GROUP BY => get rid of all subqueries which do per note_id process, simply JOIN on the wanted table and move their SUM() and other column selection in the main query SELECT
When you want to have 2 values based on the same table but with different filters, you don't need to make a subquery, you can use for ( let's say a SUM() ) example:

SUM(IF(COLUMN1 = YOUR_FILTER1 OR COLUMN1 = YOUR_FILTER2, COLUMN1, 0)) as totalWithFILTER1andFILTER2 [...] GROUP BY note_id

Last but not least, you are joining (inner) on a table which filters on TYPE.code different than 'REB' or 'PN', but then you are are joining (LEFT) on a result set which filters on TYPE.code = 'PN', which makes no sense, that left join will always result in NULL

FYI, since what i said might seem fuzzy, I started to simplify you query, but stopped on that nonsense since I didn't know what you wanted to achieve (didn't refactorized the 2 LEFT JOIN). Here is the query ( couldn't test it though ) :

SELECT
  CONCAT(cust.first_name, ' ', cust.last_name) AS customerName,
  TYPE.code                                            AS transType,
  ty1.nsfamount,
  SUM(np.rebateamount) as 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 loan_transaction trans
INNER JOIN customer cust ON trans.customer_id = cust.customer_id
INNER JOIN lookuptransactiontypes TYPE ON trans.transactiontype = TYPE.transactiontypesid
INNER JOIN note n ON trans.note_id = n.note_id
INNER JOIN note_payment 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 loan_transaction trans4
                   INNER JOIN lookuptransactiontypes type2 ON trans4.transactiontype = type2.transactiontypesid
                   WHERE (trans4.cashamount > 0
                         OR trans4.chequeamount > 0
                         OR trans4.debitamount > 0
                         OR trans4.preauthorizedamount > 0) AND type2.code = 'NSF'
             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

WHERE
(trans.cashamount < 0
OR trans.chequeamount < 0
OR trans.debitamount < 0
OR trans.preauthorizedamount < 0)
AND TYPE.code <> 'REB'
AND TYPE.code <> 'PN'

GROUP BY trans.note_id;

score 0 · Answer 2 · answered Apr 23 '18 at 18:12

I agree with @Aurelien answer, why you join to a derived table while you can join to a normal table and apply the filters. Why doing this

-- this will force a full scan on customer table and ignores the filter
select whatever 
from transactions inner join 
(
    select * from customer 
) customer on transactions.customer_id = customer.customer_id
where customer.customer_id  = 1;

While you can do this

select whatever 
from transactions inner join customer on transactions.customer_id = customer.customer_id
where customer.customer_id  = 1;

In addition to @Aurelien answer,

IMHO, the problem with your query is that you need the data of all of your customers, so no matter how this query is optimized you still doing full scans, you can't scale, imagine when you have 100M transactions after couple of years from now.

May be this is not exactly what you want, but how about partitioning/paginating a report like this. The user doesn't usually need to show the whole customers in one shot and you don't need to waste your resources.

Our plan is to do the same work, but for 50 customers only.

After you get rid of unnecessarily sub queries -as shown in @Aurelien answer - change this part of his query

FROM loan_transaction trans
    INNER JOIN customer cust ON trans.customer_id = cust.customer_id

into this

FROM (SELECT * FROM customer LIMIT 50 OFFSET 0) cust 
    INNER JOIN loan_transaction trans ON trans.customer_id = cust.customer_id

Note that pagination with offset is not scaling, so if your customers table is big, you might consider another type of pagination

MySql query taking a lot longer than it should

2 Answers2