Using UNION or LEFT OUTER JOIN?

[dark_destroyer]

Hi All,

 

I have been struggling with what seems a simple enough query.

I have three tables, Accounts, Contacts & Activities.

Everytime an Activity is logged it logs the AccountID, Contact Subject, TimeDate.

 

I query the Activities table to show me all the activity count from the previous week for each AccountID.

I use:

select
accounts.account as Account,
count(distinct activities.contactid) as Users,
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

The result is something like This:

 

Account  Users

ACME Ltd  4

Warner Bros  6

RBS   9

etc..

 

The activities table has baout 20 million rows and this runs in about 20 seconds.

 

However, I want a comprehensive list.

I want to combine the results with a list of AccountID's that havent had any activity for that month.

 

 

Account  Users

ACME Ltd  4

Warner Bros  6

RBS   9

Microsoft 0  or NULL

etc...

 

i have tried to UNION like this:

select Account, '' from Accounts
UNION
select
accounts.account as Account,
count(distinct activities.contactid) as Users
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

From what i understand about UNION is that it should return a unique list (without duplicates).

But what i get is a list of aprox 1400 accounts with when I only have approx 900 Accounts.

 

I have tried LEFT OUTER JOIN but this just seemed to run forever ( i killed it after 2 hours)

 

Does anyone have any suggestions on what I can try?

 

 

Thanks

Dark

 

 

 

[taquitosensei]

As far as I know UNION removes duplicate rows, so the count(distinct contactid) in the second part would make it not a distinct row. So in your query you're getting everything, then everything with activity in the last week.

 

 

 

Hi All,

 

I have been struggling with what seems a simple enough query.

I have three tables, Accounts, Contacts & Activities.

Everytime an Activity is logged it logs the AccountID, Contact Subject, TimeDate.

 

I query the Activities table to show me all the activity count from the previous week for each AccountID.

I use:

select
accounts.account as Account,
count(distinct activities.contactid) as Users,
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

The result is something like This:

 

Account  Users

ACME Ltd  4

Warner Bros  6

RBS   9

etc..

 

The activities table has baout 20 million rows and this runs in about 20 seconds.

 

However, I want a comprehensive list.

I want to combine the results with a list of AccountID's that havent had any activity for that month.

 

 

Account  Users

ACME Ltd  4

Warner Bros  6

RBS   9

Microsoft 0  or NULL

etc...

 

i have tried to UNION like this:

select Account, '' from Accounts
UNION
select
accounts.account as Account,
count(distinct activities.contactid) as Users
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

From what i understand about UNION is that it should return a unique list (without duplicates).

But what i get is a list of aprox 1400 accounts with when I only have approx 900 Accounts.

 

I have tried LEFT OUTER JOIN but this just seemed to run forever ( i killed it after 2 hours)

 

Does anyone have any suggestions on what I can try?

 

 

Thanks

Dark

Edited May 10, 2013 by taquitosensei

[dark_destroyer]

Hi,

 

Thanks for the reply, but the count distinct is only counting distinct contactid's not accountid's so it shouldnt affect the query?

[mikosiko]

2 questions:

 

- what is the result of this sentence when you run it in your mysql client (phpmyadmin maybe?)... post back the results.

 

EXPLAIN SELECT accounts.account as Account,
       count(distinct activities.contactid) as Users
FROM accounts
     LEFT JOIN activities ON  activities.accountid = accounts.accountid
WHERE completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
  AND completeddate <  curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
GROUP BY accounts.account;

 

- which are the defined Indexes for your tables accounts and activities

 

[taquitosensei]

Hi,

 

Thanks for the reply, but the count distinct is only counting distinct contactid's not accountid's so it shouldnt affect the query?

It does effect the query. Because in first query the value you're getting is ''   The second query you're getting a number.

[Barand]

Try

SELECT
accounts.account as Account,
COUNT(distinct activities.contactid) as Users,
from accounts
LEFT JOIN activities
    ON activities.accountid=accounts.accountid
    AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
    AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
GROUP BY accounts.account

ps INDEX completeddate if you haven't already

Edited May 10, 2013 by Barand