title: Optimise SQL query for the report
body: <p>This is SQL query I wrote, it work fine but it is slow.</p>
  
  <pre><code>SELECT D.Username, 
          SUM(CASE WHEN D.type = 'Yes' THEN 1 ELSE 0 END) as Yes, 
          SUM(CASE WHEN D.type = 'No' THEN 1 ELSE 0 END) as No, 
          SUM(CASE WHEN D.type = '' THEN 1 ELSE 0 END) as Other, 
          SUM(CASE WHEN S.mobile IS NULL THEN 0 ELSE 1 END) as Sales, 
          COUNT(*) as TOTAL FROM dairy as D
    LEFT JOIN (SELECT DISTINCT mobile FROM sales) as S on D.MobileNo = S.mobile 
          WHERE source = 'Network' AND UNIX_TIMESTAMP(CheckDate) &gt;= 1309474800 AND UNIX_TIMESTAMP(CheckDate) &lt;= 1309561200
   group by D.Username order by TOTAL DESC
  </code></pre>
  
  <p>As you can see it count number of Yes, No, Other and the matching MobileNo (<code>D.MobileNo = S.mobile</code>) sale. </p>
  
  <p>I have tried adding index to type, username, mobile, MobileNO, CheckDate and source - the performance did not improve much.</p>