The query below takes 0.6748seconds to execute, but when there are many users attempting to do the same activity at the same time, the processes becomes very slow and takes about 30seconds to execute.
Server Bandwidth: 7TB
I have made several attempts to optimize the query and the performance improved, but as the database grew larger, the performance has declined again, I am left with no more option to try.
Please assist to help optimize my query further
SELECT * FROM (SELECT * FROM
(SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated
FROM
loan_applications_tbl a
LEFT JOIN
topup_or_reapplication_tbl AS c
ON a.ippis = c.ippis
WHERE
(
(
(
(
a.current_loan = '0'
AND a.loan_status IN (
'Approved', 'Closed'
)
)
)
AND (
((a.loan_status='pending'
AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(a.date_updated) = MONTH(CURRENT_DATE)
AND YEAR(a.date_updated) = YEAR(CURRENT_DATE)
)
)
)
)
UNION ALL
SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated
FROM
loan_applications_tbl_dump b
LEFT JOIN
topup_or_reapplication_tbl c
ON b.ippis = c.ippis
WHERE
(
(
(
(
b.current_loan = '0'
AND b.loan_status IN (
'Approved', 'Closed'
)
)
)
AND (
((b.loan_status='pending'
AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(b.date_updated) = MONTH(CURRENT_DATE)
AND YEAR(b.date_updated) = YEAR(CURRENT_DATE)
)
)
)
)
UNION DISTINCT
SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated
FROM
loan_applications_tbl a
INNER JOIN
topup_or_reapplication_tbl AS c
ON a.ippis = c.ippis
WHERE
(
(
c.current_loan = '1'
AND (
((c.status='pending'
AND c.top_up_approved < 500000) OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL') ))
)
AND (
(
MONTH(c.date_updated) = MONTH(CURRENT_DATE)
AND YEAR(c.date_updated) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated
FROM
loan_applications_tbl_dump b
INNER JOIN
topup_or_reapplication_tbl c
ON b.ippis = c.ippis
WHERE
(
(
c.current_loan = '1'
AND (
((c.status='pending'
AND c.top_up_approved < 500000) OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL') ))
)
AND (
(
MONTH(c.date_updated) = MONTH(CURRENT_DATE)
AND YEAR(c.date_updated) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated
FROM
loan_applications_tbl a
LEFT JOIN
topup_or_reapplication_tbl AS c
ON a.ippis = c.ippis
WHERE
(
(
(
a.current_loan = '1'
)
AND (
((a.loan_status='pending'
AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(a.date_updated) = MONTH(CURRENT_DATE)
AND YEAR(a.date_updated) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated
FROM
loan_applications_tbl_dump b
LEFT JOIN
topup_or_reapplication_tbl c
ON b.ippis = c.ippis
WHERE
(
(
(
b.current_loan = '1'
)
AND (
((b.loan_status='pending'
AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(b.date_updated) = MONTH(CURRENT_DATE)
AND YEAR(b.date_updated) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated
FROM
loan_applications_tbl a
INNER JOIN
topup_or_reapplication_tbl AS c
ON a.ippis = c.ippis
WHERE
(
(
c.current_loan = '1'
AND (
((c.status='pending'
AND c.top_up_approved < 500000) OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL') ))
)
AND (
(
MONTH(c.date_created) = MONTH(CURRENT_DATE)
AND YEAR(c.date_created) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated
FROM
loan_applications_tbl_dump b
INNER JOIN
topup_or_reapplication_tbl c
ON b.ippis = c.ippis
WHERE
(
(
c.current_loan = '1'
AND (
((c.status='pending'
AND c.top_up_approved < 500000) OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL') ))
)
AND (
(
MONTH(c.date_created) = MONTH(CURRENT_DATE)
AND YEAR(c.date_created) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated
FROM
loan_applications_tbl a
LEFT JOIN
topup_or_reapplication_tbl AS c
ON a.ippis = c.ippis
WHERE
(
(
(
(
a.current_loan = '0'
AND a.loan_status IN (
'Approved', 'Closed'
)
)
)
AND (
((a.loan_status='pending'
AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(a.date_created) = MONTH(CURRENT_DATE)
AND YEAR(a.date_created) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated
FROM
loan_applications_tbl_dump b
LEFT JOIN
topup_or_reapplication_tbl c
ON b.ippis = c.ippis
WHERE
(
(
(
(
b.current_loan = '0'
AND b.loan_status IN (
'Approved', 'Closed'
)
)
)
AND (
((b.loan_status='pending'
AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(b.date_created) = MONTH(CURRENT_DATE)
AND YEAR(b.date_created) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated
FROM
loan_applications_tbl a
LEFT JOIN
topup_or_reapplication_tbl AS c
ON a.ippis = c.ippis
WHERE
(
(
(
a.current_loan = '1'
)
AND (
((a.loan_status='pending'
AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(a.date_created) = MONTH(CURRENT_DATE)
AND YEAR(a.date_created) = YEAR(CURRENT_DATE)
)
)
)
)
UNION
DISTINCT SELECT
c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated
FROM
loan_applications_tbl_dump b
LEFT JOIN
topup_or_reapplication_tbl c
ON b.ippis = c.ippis
WHERE
(
(
(
b.current_loan = '1'
)
AND (
((b.loan_status='pending'
AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL',
'NIL', 'NIL', 'NIL' ) ))
)
AND (
(
MONTH(b.date_created) = MONTH(CURRENT_DATE)
AND YEAR(b.date_created) = YEAR(CURRENT_DATE)
)
)
)
)
) t3 ORDER BY t3.date_updated, t3.tu_date_updated DESC LIMIT 18446744073709551615) AS t4 GROUP BY t4.ippis
EXPLAIN statement gives me the table below:
CodePudding user response：
That's a very large query. Maybe even too large for a Stack Overflow question.
I suggest you try optimizing one of the subqueries at a time (one of the arms of your UNION operation). That way you don't have to think about the whole mess at once.
Use UNION ALL rather than UNION DISTINCT if you can; UNION DISTINCT deduplicates its result set and that takes cpu time and RAM.
You have this WHERE-clause pattern recurring several times.
MONTH(a.date_updated) = MONTH(CURRENT_DATE) AND YEAR(a.date_updated) = YEAR(CURRENT_DATE)It's not sargable. That is, it's written to defeat the use of an index on
loan_applications_tbl.date_updated. Use this equivalent instead. If you do MySQL will be able to do a range scan on the index.a.date_updated >= LAST_DAY(CURRENT_DATE) 1 DAY - 1 MONTH AND a.date_updated < LAST_DAY(CURRENT_DATE) 1 DAYLAST_DAY(CURRENT_DATE) gets you midnight on the last day of the month, and the rest of the date arithmetic gets midnight on the first day of the present month and midnight on the first day of the next month.
You didn't tell us anything about your indexes. Your query has ORs which make them slower, so you'll have to experiment with this. Try creating this index, if you don't have it already.
ALTER TABLE loan_applications_tbl ADD INDEX cur_stat_date (current_loan, loan_status, date_updated, appr)It might help, as it allows MySQL to random-access the index I suggested, then read it sequentially.
You'll need a similar index on
loan_applications_tbl_dump.When you have a lot of users, you may be seeing contention. If your application can tolerate retrieving data that may not be exactly right for rows that are currently being inserted and updated, give this command right before your query.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;This can be a bit risky for data integrity if your application uses a lot of transaction. But it will reduce contention.
Can you refactor the query to be less repetitive?
CodePudding user response：
These might help:
c: INDEX(current_loan, status, top_up_approved, ippis)
a, b: INDEX(current_loan, loan_status, appr, ippis)
Splitting up a date is usually inefficient:
AND MONTH(b.date_created) = MONTH(CURRENT_DATE)
AND YEAR(b.date_created) = YEAR(CURRENT_DATE)
-->
AND b.date_created >= LEFT(CURDATE(), 7)
That should allow for these indexes to be useful:
b, c: INDEX(current_loan, date_created, ippis)
That may speed things up by starting with c