Use Indexes to optimise my query OR Mysql

Question

I have read through a lot of documentation on how to use EXPLAIN and indexes on sitepoint and mysql documentation website. Even found a few PDFs. But my query seems a bit complex than all the example I have come across. I have been trying to optimize my query to work more efficiently for almost 1 week.

As of now, execution time is 56s on 11000 records.

Can someone help to rewrite my query or guide me on how I can use indexes on it? See my code below:


SELECT t3.*, topup_or_reapplication_tbl.loan_id, topup_or_reapplication_tbl.user_unique_id AS tu_user_unique_id, 
          topup_or_reapplication_tbl.ippis AS tu_ippis, topup_or_reapplication_tbl.top_up_approved, 
           topup_or_reapplication_tbl.loan_type AS tu_loan_type, topup_or_reapplication_tbl.dse, topup_or_reapplication_tbl.status, topup_or_reapplication_tbl.current_loan AS tu_current_loan, 
          topup_or_reapplication_tbl.record_category, topup_or_reapplication_tbl.date_created AS tu_date_created, 
          topup_or_reapplication_tbl.date_updated AS tu_date_updated
        FROM (
        SELECT loan_applications_tbl.id, loan_applications_tbl.user_unique_id, loan_applications_tbl.loan_location, 
          loan_applications_tbl.ippis, loan_applications_tbl.tel_no,
          loan_applications_tbl.organisation, loan_applications_tbl.branch, loan_applications_tbl.loan_agree, 
          loan_applications_tbl.loan_type, loan_applications_tbl.appr, loan_applications_tbl.sold, 
          loan_applications_tbl.loan_status, loan_applications_tbl.top_up, loan_applications_tbl.current_loan, 
          loan_applications_tbl.date_created, loan_applications_tbl.date_updated 
        FROM loan_applications_tbl 
        UNION ALL
          SELECT loan_applications_tbl_dump.loan_id, loan_applications_tbl_dump.user_unique_id,     
          loan_applications_tbl_dump.loan_location, loan_applications_tbl_dump.ippis, loan_applications_tbl_dump.tel_no,
          loan_applications_tbl_dump.organisation, loan_applications_tbl_dump.branch, loan_applications_tbl_dump.loan_agree, 
          loan_applications_tbl_dump.loan_type, loan_applications_tbl_dump.appr, loan_applications_tbl_dump.sold, 
          loan_applications_tbl_dump.loan_status, loan_applications_tbl_dump.top_up, loan_applications_tbl_dump.current_loan, 
          loan_applications_tbl_dump.date_created, loan_applications_tbl_dump.date_updated 
          FROM loan_applications_tbl_dump ) t3

        LEFT JOIN topup_or_reapplication_tbl
        ON t3.ippis=topup_or_reapplication_tbl.ippis   
        
        
        
        WHERE ((topup_or_reapplication_tbl.status IN ('pending', 'corrected', 'Rejected', 'Processing', 
        'Captured', 'Reviewed', 'top up') 
        
        AND MONTH(CURRENT_DATE) IN (MONTH(topup_or_reapplication_tbl.date_created), MONTH(topup_or_reapplication_tbl.date_updated) 
        AND YEAR(CURRENT_DATE) IN (YEAR(topup_or_reapplication_tbl.date_created), YEAR(topup_or_reapplication_tbl.date_updated)) 
        AND   topup_or_reapplication_tbl.current_loan='1'
        )) 

        OR ( t3.loan_status IN ('pending', 'corrected', 'Rejected', 'Processing', 
        'Captured', 'Reviewed', 'top up')
        
        AND MONTH(CURRENT_DATE) IN (MONTH(t3.date_created), MONTH(t3.date_updated) )
        AND YEAR(CURRENT_DATE) IN (YEAR(t3.date_created), YEAR(t3.date_updated)) 
        AND (t3.current_loan='1' OR (t3.current_loan='0' AND t3.loan_status IN('Approved','Closed')))
            
           ))
        
        -- GROUP BY t3.ippis ORDER BY t3.date_updated DESC

Replace UNION ALL in subquery with UNION ALL of 2 separate queries. Then optimise each separate query independently. — Akina, May 20 '21 at 07:06
@Akina Thanks for the clue. Do you mean that I should do the LEFT JOIN of loan_applications_tbl with topup_or_reapplication_tbl and loan_applications_tbl_dump with topup_or_reapplication_tbl, then do a UNION ALL of the result of both? — Peter, May 20 '21 at 07:15
I mean that you must write a query which does not use `loan_applications_tbl_dump` and another query which does not use `loan_applications_tbl`, optimise each query independently (it is possible that optimal queries will look differently due to the difference in indices and/or statistic), then UNION ALL them. — Akina, May 20 '21 at 07:29
@Akina Thanks again...but do you mind doing something like a pseudocode to help me understand your answer better — Peter, May 20 '21 at 07:39

score 1 · Answer 1 · answered May 20 '21 at 07:42

1

do you mind doing something like a pseudocode to help me understand your answer better – Peter

SELECT {fieldset}
FROM ( SELECT {fieldset} FROM table1
     UNION ALL
       SELECT {fieldset} FROM table2 )
JOIN {tableset}
WHERE {conditions}

is equal to

( SELECT {fieldset}
  FROM table1
  JOIN {tableset}
  WHERE {conditions} )
UNION ALL
( SELECT {fieldset}
  FROM table2
  JOIN {tableset}
  WHERE {conditions} )

answered May 20 '21 at 07:42

Akina

39,301
5
14
25

Thanks a million for your help. I have done this and the performance does not seem to improve, do you have some recommendations on how I could use composite indexing considering my `WHERE` clause? Could that be the next thing I need to do to optimise it further? I need your help please. – Peter May 20 '21 at 07:53
@Peter I think that there will be useful to create new topic with one query to be improved. Post there not only query text but complete CREATE TABLE for all tables, the execution plan, and tables statistic related to the conditions. – Akina May 20 '21 at 08:00
I just did as you advised, I have asked a question based on one of the queries. Can you help please? https://stackoverflow.com/questions/67618321/optimise-query-indexing-explain-mysql – Peter May 20 '21 at 10:15

score 1 · Accepted Answer · answered May 21 '21 at 00:49

This may help some:

      AND  MONTH(CURRENT_DATE) IN (MONTH(t3.date_created), MONTH(t3.date_updated) )
      AND  YEAR(CURRENT_DATE) IN (YEAR(t3.date_created), YEAR(t3.date_updated))

-->

      AND t3.date_created > CURDATE() - DAYOFMONTH(CURDATE())-1 DAY
      AND t3.date_updated > CURDATE() - DAYOFMONTH(CURDATE())-1 DAY

Probably you could get rid of the updated test since it will always be later than created?

But perhaps there real fix is not to have 3 essentially identical tables.

Use Indexes to optimise my query OR Mysql

2 Answers2