Reputation: 11
MySql query taking a lot longer than it should
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
Upvotes: 1
Views: 100
Answers (2)
Reputation: 7483
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
Upvotes: 0
Reputation: 1507
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;
Upvotes: 1
Related Questions
- MySQL query takes a long time to run
- SQL Query taking a long time to run
- Why is this query taking so long to execute
- SQL Query taking far too long
- MySQL query too slow?
- MySql query keep running for a long time
- MySQL Query Taking a Long Time
- Why is this MySQL query taking so long and how can I fix it?
- MySQL query takes time to run
- Why is this query taking so long?