Optimise SQL query for the report

Question

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.

can u change the query to check against CheckDate without using function, because with this function wrap the index of this column not so effective, look on http://stackoverflow.com/questions/95183/how-does-one-create-an-index-on-the-date-part-of-datetime-field-in-mysql — Haim Evgi, Jul 28 '11 at 13:30
Can you post the output of `SHOW CREATE TABLE diary`? And the same for sales? Then, the output of your SELECT, but with `EXPLAIN` in front of it? — Konerak, Jul 28 '11 at 13:33

Adriano Carneiro · Accepted Answer · 2011-07-28T15:07:22.390

2

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.

edited Jul 28 '11 at 15:07

answered Jul 28 '11 at 13:38

Adriano Carneiro

57,693
12
90
123

Thanks for SQL query, it dont seem to work - im not getting any result. – I'll-Be-Back Jul 28 '11 at 13:58
Sorry, its working now, my mistake... but the result appear to be different... It is not adding up properly. – I'll-Be-Back Jul 28 '11 at 14:18
Is it possible that there are D.MobileNo that will contain a value not in `SELECT DISTINCT mobile FROM sales`? – Adriano Carneiro Jul 28 '11 at 14:37

score 0 · Answer 2 · answered Jul 28 '11 at 13:37

If this is time critical it can also make sense to store more data.

Here this means that you add INT columns for YesValue, NoValue, OtherValue and fill them with 0 or 1 in your application. By doing this you can remove the case calculations from your SELECT-part.

Also please post all indexes currently available and as the comments say the CREATE-statement for the table.

Optimise SQL query for the report

2 Answers2

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

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

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