#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    Count Distinct Taking Too Long


    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:

    PHP Code:
    select count(distinct contactidfrom 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:
    PHP Code:
    select accountidcount(distinct contactidfrom activities where completeddate like '2013-02%' group by accountid
    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?

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


    Thanks,
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    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 --
    Code:
     WHERE completeddate >= '2013-02-01'
       AND completeddate  < '2013-03-01'
    second, you gave no information on the indexes on your table... please do a SHOW CREATE TABLE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    Legend... that ran in 1m 16s, perfect, thanks for the tip!

IMN logo majestic logo threadwatch logo seochat tools logo