March 5th, 2013, 11:06 AM
Count Distinct Taking Too Long
I have a table with about 15 million rows.
The table has several fields but the two i am concerned with are ContactID and AccountID.
If i run this query:
it runs fine, takes about 3 minutes to run.
select count(distinct contactid) from activities where completeddate like '2013-02%';
it gives me the count of every distinct contactid from that month.
but i want to be able to count the number of distinct contactid's per accountid to give me a list of something like:
then i try:
but this just seems to go into an a very long cycle, so far its been running for about 6 hours, i would have thought that since it can count the number of distinct contactid's then it would have been straightforward to introduce an additional count?
select accountid, count(distinct contactid) from activities where completeddate like '2013-02%' group by accountid;
Can anyone suggest a more economical way to run this type of query?
March 5th, 2013, 11:14 AM
i see two problems...
first, never use LIKE with DATE or DATETIME columns, because you are forcing a conversion from internal date/datetime format to character string (this might be one of the reasons your query is so slow)
instead, do this --
second, you gave no information on the indexes on your table... please do a SHOW CREATE TABLE
WHERE completeddate >= '2013-02-01'
AND completeddate < '2013-03-01'
March 5th, 2013, 11:57 AM
Legend... that ran in 1m 16s, perfect, thanks for the tip!