Need to optimize below mysql query

Question

Need to find COUNT OF unique user accounts from table for last 5 months, this table contains millions of data.

select COUNT(distinct(account)) as total_unique,
 COUNT(distinct(IF( Column1!=0 OR Column2!=0, account, null)))
from table_name where date(event_date) >= date('2014-04-01') and date(event_date) <=date('2014-08-31');

This query currently consuming more than 10 mins to get the data. We have indexes on 'event_date' column and on 'account' column in this table. We are using mysql as DB. Could you please help us.

Gervs · Accepted Answer · 2014-10-06 16:20:53Z

3

In your query indexes can't be use because of date function over the eventdate colomn

WHERE eventdate BETWEEN CAST('2014-04-01' AS DATE)
                        AND CAST('2014-09-01' AS DATE) - INTERVAL 1 SECOND

You'll get same result but now an index range scan will be used.

Or as ypercube stated in his comment

WHERE eventdate >= CAST('2014-04-01' AS DATE)
  AND eventdate < CAST('2014-09-01' AS DATE)

edited Oct 6, 2014 at 16:20

answered Oct 6, 2014 at 13:52

Gervs

1,3979 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ypercubeᵀᴹ Over a year ago

Good. Even better, in my opinion: WHERE eventdate >= CAST('2014-04-01' AS DATE) AND eventdate < CAST('2014-09-01' AS DATE)

Gervs Over a year ago

I agree, i will change it

Hrushi Over a year ago

Hrushi Over a year ago

Also tried below where clause as per the suggestion of ypercube still having same issue - WHERE cast(event_date as date) >= CAST('2014-05-31' AS DATE) - INTERVAL 5 MONTH AND cast(event_date as date) < CAST('2014-05-31' AS DATE) and cast(event_date as DATE) < NOW() - INTERVAL 2 DAY GROUP BY MONTH(event_date);

JNevill · Accepted Answer · 2014-10-06 14:13:36Z

-1

If column1 and column2 are the same for each repeated record of account, you should consider sticking the SELECT and GROUP BY of the account, column1, and column2 in derived table. This will help with the expensive task of Count and will remove the really expensive Count(Distinct())

SELECT 
    Count(a.Account) AS total_unique,
    SUM(CASE WHEN a.column1 <> 0 or a.column2 <> 0 THEN 1 ELSE 0 END)    
FROM 
   (
        SELECT account, column1, column2 
        FROM table_name GROUP BY 1,2,3
        WHERE 
             DATE (event_date) >= DATE ('2014-04-01') AND 
             DATE (event_date) <= DATE ('2014-08-31')) as a
   );

edited Oct 6, 2014 at 14:13

answered Oct 6, 2014 at 13:35

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

This will produce an error (event_date not a column of derived table a)

Collectives™ on Stack Overflow

Need to optimize below mysql query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related