title: Optimise query (Indexing, EXPLAIN) Mysql
body: <p>Based on an advise from another developer here on stackoverflow, I have updated my query as below, but I still need to optimise it further. Can someone guide my on how best I can apply indexing to the query.</p>
  <p>See Query Below:</p>
  <pre><code>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 AS tu_user_unique_id, 
            c.ippis AS tu_ippis, c.top_up_approved, 
             c.loan_type AS tu_loan_type, c.dse, c.status, c.current_loan AS tu_current_loan, 
            c.record_category, c.date_created AS tu_date_created, 
            c.date_updated AS tu_date_updated 
  FROM loan_applications_tbl a
  LEFT JOIN topup_or_reapplication_tbl c
      ON a.ippis=c.ippis   
  WHERE ((c.status IN ('pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up') 
         AND MONTH(CURRENT_DATE) IN (MONTH(c.date_created), MONTH(c.date_updated) 
         AND YEAR(CURRENT_DATE) IN (YEAR(c.date_created), YEAR(c.date_updated)) 
         AND   c.current_loan='1' )) 
  OR ( a.loan_status IN ('pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up')
       AND MONTH(CURRENT_DATE) IN (MONTH(a.date_created), MONTH(a.date_updated)) )
       AND YEAR(CURRENT_DATE) IN (YEAR(a.date_created), YEAR(a.date_updated)) 
       AND (a.current_loan='1' 
            OR (a.current_loan='0' 
                AND a.loan_status IN('Approved','Closed')))))
  </code></pre>
  <p>Execution time: 53s</p>
  <p>No of records: 11000</p>
  <p>using mysql EXPLAIN gives the screenshot below: (How do I maximise the information in the possible_keys column</p>
  <p><a href="https://i.stack.imgur.com/CKulA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CKulA.png" alt="EXPLAIN gives the screenshot below: (How do I maximise the information in the possible_keys column)" /></a></p>
  <p>I HAVE UPDATED ADDITIONAL INFORMATION BELOW:</p>
  <p>I am using the OR between c and a for the below reasons:</p>
  <ol>
  <li><p><code>a</code> is the parent table with 66 columns which gets populated with loan entries, if a new entry on <code>a</code> has a matching/existing <code>ippis</code> (Unique field on <code>a</code>) some columns in <code>a</code> are updated/overwriten with data from the new entry, while the remaining data in the entry are inserted as new rows in <code>c</code> (<code>ippis</code> is not unique in table <code>c</code>). This is to keep a history of all subsequent loan requests  while not giving room for redundancy</p>
  </li>
  <li><p>While retrieving records I needed the big <code>OR</code> clause to enable me check both <code>a</code> and <code>c</code> tables for all instances of each loan records where the <code>status, date and current_loan</code> columns match the parameters in my WHERE clause.</p>
  </li>
  <li><p><code>a</code> will ALWAYS have a complete record in it but <code>c</code> will NOT ALWAYS have a record in it, except there are more loan requests for the same unique ID. <code>a</code> contains <strong>the &quot;who is the account person such as by unique ID&quot;, and the additional / supplemental status detail FOR THE FIRST LOAN, subsequently, after the first loan &quot;c&quot; will be additional / supplemental status detail of the actual loan applications with the same Unique ID</strong>.</p>
  </li>
  <li><p><strong>if &quot;A&quot; is created Mar 12, and a new &quot;c&quot; record is created on Mar 16. The &quot;A&quot; record also gets the last updated stamped with Mar 16 since it has a child attachment that has some impact against it</strong>, while the new <code>c</code> record has it own created and updated time stamps. The Updated field will be blank/null for the <code>a</code> record until changes are made or there is a <code>c</code> record, The Updated field will be blank/null for <code>c</code> record until there's some changes made to <code>c</code> record</p>
  </li>
  </ol>
  <p>I hope this is understandable</p>