Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: Count Distinct Issue

    Hi All,

    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:
    select count(distinct contactid) from activities where completeddate like '2013-02%';

    it runs fine, takes about 3 minutes to run.
    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:

    AccountID Count
    WQEAFAD 5
    EQWRTYE 6
    KOLKOKKU 7
    etc...

    then i try:
    select accountid, count(distinct contactid) from activities where completeddate like '2013-02%' group by accountid;

    but this just seems to go into an a very long report, 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?

    Can anyone suggest a more economical way to run this type of query?


    Thanks,
    dc

  2. #2
    Join Date
    Dec 2012
    Posts
    25
    the query could be faster by increasing the 'temp_table_size' of your mysql!!

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Not sure how increasing the temp_table_size will help? Is completedate a date data type or a string? If it is a date, then I would suggest changing the query to look at completedate >= '2013-02-01' AND completedate < '2013-03-01'

    If you want to speed up the overall processing you will need to create an index on completedate, accountid and contactid. Then it will use only the index to return the valid values without having to go back to the original table data. This makes processing far more efficient.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •