CODEWITHSUNDEEP
mysqlsqldatabasesql-optimization
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

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.

Upvotes: 1

Views: 134

Answers (2)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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.

Upvotes: 2

ZoolWay
ZoolWay

Reputation: 5505

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.

Upvotes: 0

Related Questions