Optimize my query (indexes, EXPLAIN) Mysql

Question

With the help of developers here on stackoverflow, I have been able to optimize my query to perform better than it was initially doing. Execution time dropped to: 1.2s

However, after doing EXPLAIN on the query, it seemed like the loan_applications_tbl ( date_updated, loan_status, current_loan, ippis ) indexes were not being used, as the type column says ALL for table a. What might be the cause for this please? Why is the index for this table being ignored and how do I fix this?

See my query below:

 EXPLAIN 
SELECT 
        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.top_up, 
        a.current_loan, 
        a.date_created, 
        a.date_updated, 
        c.loan_id, 
        c.user_unique_id tu_user_unique_id, 
        c.ippis tu_ippis, 
        c.top_up_approved,
        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 
    FROM 
        -- this creates inline mySQL variables I can use for the WHERE condition
        -- by doing comma after with no explicit join, it is a single row
        -- and thus no Cartesian result, just @variables available now
        ( select 
                -- first truncating any TIME portion by casting to DATE()
                @myToday := date(curdate()),
                -- now subtract day of month -1 to get first of THIS month
                @beginOfMonth := date_sub( @myToday, interval dayOfMonth( @myToday ) -1 day ),
                -- and now, add 1 month for beginning of next
                @beginNextMonth := date_add( @beginOfMonth, interval 1 month ) ) SqlVars,

        loan_applications_tbl a
    
            LEFT JOIN topup_or_reapplication_tbl c
                ON  a.ippis = c.ippis   
                AND c.current_loan='1'
                AND c.status IN ('pending', 'corrected', 'Rejected', 
                                'Processing', 'Captured', 'Reviewed', 'top up') 

                
                AND @beginOfMonth <= c.date_updated
                AND c.date_updated < @beginNextMonth
    WHERE
            -- forces only activity for the single month in question
            -- since the "a" table knows of any "updates" to the "C",
            -- its updated basis will keep overall restriction to any accounts
            -- updated within this month in question only
            @beginOfMonth <= a.date_updated 
        AND a.date_updated < @beginNextMonth
        
        -- and NOW we can easily apply the OR without requiring
        -- to run against the ENTIRE set of BOTH tables.
        AND (
                    c.ippis IS NOT NULL
                OR 
                    ( a.loan_status IN (  'pending', 'corrected', 'Rejected', 'Processing', 
                            'Captured', 'Reviewed', 'top up')
                    AND (   
                            a.current_loan = '1' 
                        OR  (   a.current_loan = '0' 
                            AND a.loan_status IN ('Approved', 'Closed')
                            )
                        )
                    )
            )

Indexes used:

loan_applications_tbl ( date_updated, loan_status, current_loan, ippis )
topup_or_reapplication_tbl ( ippis, status, current_loan, date_updated )

EXPLAIN on the query gives the screenshot below:

Answer 1

Try https://dev.mysql.com/doc/refman/8.0/en/index-hints.html#:~:text=The%20USE%20INDEX%20(%20index_list%20)%20hint,some%20particular%20index%20or%20indexes.

eg

SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;