Optimise SQL query for the report

This is SQL query I wrote, it work fine but it is slow.

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) >= 1309474800 AND UNIX_TIMESTAMP(CheckDate) <= 1309561200
 group by D.Username order by TOTAL DESC

As you can see it count number of Yes, No, Other and the matching MobileNo (D.MobileNo = S.mobile) sale.

I have tried adding index to type, username, mobile, MobileNO, CheckDate and source - the performance did not improve much.

Solution

Three points to notice in your query:

1. There's a chance the `LEFT JOIN` is giving you performance issues.

However, you need it, since it is possible that there are D.MobileNo values that will not be present in SELECT DISTINCT mobile FROM sales. Any other work around (yes, there are options) will most likely decrease performance. But your performance might be improved by observing the next items.

2. Make sure you have indexes in the key columns:

D.type
S.mobile
D.MobileNo
D.Username
D.Source
D.CheckDate

3. You might be having problems with filtering by `UNIX_TIMESTAMP(CheckDate)`

This might be the key issue. You might be having problems with filtering by UNIX_TIMESTAMP(CheckDate) instead of CheckDate, specially if Dairy has a large amount of records. The problem is that even if you have an index for CheckDate, it will probably not be used because of the function. Try to filter by CheckDate itself.