title: How to tune UNION ALL query?
body: <p>The following MySQL query results sum of credit and debit for each account code (<code>acctcode</code>) and produces over all total using <code>union all</code>. The table <code>ledg_post</code> has 5.7 million records and is indexed. Still the query takes 1 minute to execute. Please help me to tune this query.</p>
  
  <pre><code>select b.acnt_code as acctcode 
  ,      b.disp_name as acctname
  ,      sum(amt_dr) as debit
  ,      sum(amt_cr) as credit
  ,      (sum(amt_dr)- sum(amt_cr)) as closingbalance
  ,      a.txn_code as txn_code
  from   ledg_post a
  ,      gl_acnts b
  ,      mst_loan lmt
  where  a.acnt_code = b.acnt_code 
  group 
  by     b.acnt_code
  union all
  select  ' ' acctcode
  ,       ' Grand Total ' acctname
  ,       sum(amt_dr) debit
  ,       sum(amt_cr) credit
  ,       (sum(amt_dr)- sum(amt_cr)) closingbalance
  ,       '' txn_code
  from    ledg_post a
  ,       mst_loan  lmt
  where   lmt.loan_id = a.ref_id
  </code></pre>
  
  <h2>Table Definitions</h2>
  
  <pre><code> create table ledg_post 
   ( txn_code  int(11) 
   , ref_id    int(11)
   , acnt_code int(11) 
   , amt_dr    decimal(20, 2) 
   , amt_cr    decimal(20, 2) 
   );
  
   create table gl_acnts 
   ( glm_acnt_code      int
   , glm_acnt_disp_name varchar(50) 
   );
  
   create table mst_loan 
   ( lmt_loan_id int(11) 
   , lmt_clnt_id int(11) 
   );
  </code></pre>