By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,877 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,877 IT Pros & Developers. It's quick & easy.

count(distinct) query too slow

P: 2
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.
Sep 26 '06 #1
Share this Question
Share on Google+
1 Reply


bplacker
100+
P: 121
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?
Sep 29 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.