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

Answer 1

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.