count(distinct) query too slow

[aps786]

Hi,

There is a table where I store ipaddress and user who logged in from that IP. I have a query to findout all ipaddresses, from where diff users had made request.

stat
------------
ip
userid

I wrote query like this

select ip,count(distinct userid) as count from stat group by ip having count > 1

But this query always timeout or take more than 5 minutes. I have 5 lakh rows in table.

Is there any way to do this? Is count(distinct) query always slow for large table? Same query is working on small table

P.S. I have a index on userid field as well as ip field.

[bplacker]

Hi,

There is a table where I store ipaddress and user who logged in from that IP. I have a query to findout all ipaddresses, from where diff users had made request.

stat
------------
ip
userid

I wrote query like this

select ip,count(distinct userid) as count from stat group by ip having count > 1

But this query always timeout or take more than 5 minutes. I have 5 lakh rows in table.

Is there any way to do this? Is count(distinct) query always slow for large table? Same query is working on small table

P.S. I have a index on userid field as well as ip field.

why are you saying "distinct userid"? Isn't the UserID a unique field anyways?