title: How can I determine the best place to add indexes in mysql?
body: <p>I've tried doing some searching, but I am having a bit of trouble getting a grasp on this.  </p>
  
  <p>Once I explain a query, how can I look at the information and determine the best place to add an index to speed up the query.  </p>
  
  <p>For example I have this query:</p>
  
  <pre><code>    SELECT a.app_id, DATE_FORMAT(app_datetime, '%c/%d/%y %H:%i') as app_datetime, app_language
          , if((select count(w.app_id) from li_app_cnc as w where w.app_id = a.app_id) &gt; 0 ,         concat('CNC: ',(select cnc_note from li_app_cnc as w where w.app_id = a.app_id)), 
              if((select count(x.app_id) from li_app_dnc as x where x.app_id = a.app_id) &gt; 0, concat('DNC: ', (select cancel_note from li_app_dnc as x where x.app_id = a.app_id)), 
                  if((select count(y.app_id) from li_app_canceled as y where y.app_id = a.app_id) &gt; 0, concat('Canceled: ', (select cancel_note from li_app_canceled as y where y.app_id = a.app_id)),
                      concat(h.emp_firstname, ' ', h.emp_lastname)))) as int_id, app_notes, app_facility, app_department
          , app_requesting_person, app_service_provider
          , cast(AES_DECRYPT(les_name, '$privatekey') as char) as les_name
          , les_dob, cast(AES_DECRYPT(les_medicaid_id, '$privatekey') as char) as les_medicaid_id
          , billing_total_time, billing_workorder_received, billing_admin_fee
          , billing_notes, created_by, created_on, modified_by, modified_on, wo_entered_by
          , t.cancel_code, t.cancel_note
          , u.cnc_code, u.cnc_note
          , v.cancel_code as dnc_code, v.cancel_note as dnc_note
          FROM li_appointments.li_appointments as a
          left Join orangehrm_li.hs_hr_employee as h on a.terp_id = h.employee_id
          left Join li_appointments.li_app_canceled as t on t.app_id = a.app_id
          left Join li_appointments.li_app_cnc as u on u.app_id = a.app_id
          left Join li_appointments.li_app_dnc as v on v.app_id = a.app_id
          where (app_client_id in (select account_number from li_appointments.li_client_access 
              where id = $userid) or created_by = '$username') 
              and date(app_datetime) = date(now())
              and a.app_id  not in (select f.app_id from li_app_dnc as f)
  </code></pre>
  
  <p>Which takes about 14 secconds</p>
  
  <p>It is explained as:</p>
  
  <pre><code>   1    PRIMARY                 a   ALL                 37539   Using where
     1    PRIMARY                 h   ALL                 1036    
     1    PRIMARY                 t   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
     1    PRIMARY                 u   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
     1    PRIMARY                 v   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
     9    DEPENDENT SUBQUERY  f   unique_subquery PRIMARY PRIMARY 4   func    1   Using index
     8    DEPENDENT SUBQUERY  li_client_access    ALL                        72   Using where
     7    DEPENDENT SUBQUERY  y   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
     6    DEPENDENT SUBQUERY  y   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   Using index
     5    DEPENDENT SUBQUERY  x   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
     4    DEPENDENT SUBQUERY  x   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   Using index
     3    DEPENDENT SUBQUERY  w   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
     2    DEPENDENT SUBQUERY  w   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   Using index
  </code></pre>