1

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: enter image description here

Peter
  • 60
  • 8
  • pls show the **EXPLAIN** OF YOUR QUERY – Bernd Buffen Nov 11 '21 at 18:12
  • @BerndBuffen The table in the image above shows the EXPLAIN of the query – Peter Nov 11 '21 at 18:13
  • Did you check the server load avarage when there are many users loged on? – Ergest Basha Nov 11 '21 at 18:14
  • @ErgestBasha overall CPU usage has been less than 30%. Server internet speed is about 1.6Gbps., the number of users logged in per time are less than 100 – Peter Nov 11 '21 at 18:18
  • @Peter - can you post the result from **SHOW ENGINE INNODB STATUS;** when the server use long time – Bernd Buffen Nov 11 '21 at 18:21
  • @BerndBuffen User activity is lesser on the application now, I will post this required information as soon as the server slows, are there other things I can try, pending the time I am able to get that information? – Peter Nov 11 '21 at 18:28
  • @Peter - is it possible to send me a sql dump of your tables with create tables to my e-mail. you can find it in my profile. the i can optimize your query. i am also want to see the server my.cnf and version of mysql/mariadb server – Bernd Buffen Nov 11 '21 at 19:32
  • @BerndBuffen doing that in a moment, thanks a lot – Peter Nov 11 '21 at 19:37
  • @Peter, did you want to send me something else? I haven't received anything yet – Bernd Buffen Nov 12 '21 at 18:41
  • Please post SHOW CREATE TABLE (for a b c); so we can see your current structure and we will also know the indexes you have available at this time. – Wilson Hauck Nov 13 '21 at 21:27
  • @Bernd Buffen please accept my apologies for the delay, my schedule has been traumatic. But I'm back on this now, sending an email in a moment – Peter Nov 22 '21 at 06:47
  • @WilsonHauck Well noted – Peter Nov 22 '21 at 06:49
  • @peter Does 'Well noted' mean you will post SHOW CREATE TABLE (for a b c) as requested - when time permits? – Wilson Hauck Nov 22 '21 at 11:00

2 Answers2

1

That's a very large query. Maybe even too large for a Stack Overflow question.

  1. 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.

  2. Use UNION ALL rather than UNION DISTINCT if you can; UNION DISTINCT deduplicates its result set and that takes cpu time and RAM.

  3. 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 DAY

    LAST_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.

  4. 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.

  5. 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.

  6. Can you refactor the query to be less repetitive?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

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

Rick James
  • 135,179
  • 13
  • 127
  • 222