Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySQL table reqs with (this is simplified from actual):

| Field                 | Type                  | Null | Key | Default | Extra |
+-----------------------+-----------------------+------+-----+---------+-------+
| time                  | datetime              | YES  |     | NULL    |       |
| user                  | varbinary(40)         | YES  |     | NULL    |       |
| foo                   | mediumint(8) unsigned | YES  |     | NULL    |       |
| bar                   | varbinary(20)         | YES  |     | NULL    |       |
| other fields          | ...

I want to find duplicate user-foo-bar combinations near one another in time — say, within 60 seconds of one another. So I want something like

select [whatever] from reqs inner join (
select user,foo,bar, count(*) as count from reqs
group by user,foo,bar having count>1
) as dups
where dups.user=reqs.user
and dups.foo = reqs.foo
and dups.bar=reqs.bar
and [something indicating the times are near each other];

— or at least that's the idea (in other words, I'm not sure whether the above is the way to structure the query, but the above is representative of the result I want from the query).

How would I do this?

Note that reqs is a huge table, so minimizing joins would be ideal.

share|improve this question

1 Answer 1

The problem is, when you GROUP on user,foo,bar you are not able to get a list of each of those times to compare.

Something like this might work, but no idea on the performance on a large table. You might want to only do this on a subset (ie tuples just from the last 30 days)

SELECT A.*,B.time FROM reqs A
INNER JOIN (SELECT user,foo,bar,time FROM reqs) B 
 USING (user,foo,bar)
WHERE A.time!=B.time AND ABS(TIMESTAMPDIFF(SECOND, TIMESTAMP(A.time), TIMESTAMP(B.time))) < 60
GROUP BY A.user,A.foo,A.bar,A.time;
share|improve this answer
    
There I go again, overthinking the problem. I think you got the silver bullet on this one !!! +1 !!! –  RolandoMySQLDBA Sep 23 '11 at 2:03
    
+1; thank you! This looks good; I'll try it. –  msh210 Sep 23 '11 at 14:42
    
When comparing where A.user=B.user and A.foo=B.foo and A.bar=B.bar (I actually have not three but five columns to check for equality), would it be cheaper to use where strcmp(concat(a.user,a.foo,a.bar),concat(b.user,b.foo,b.bar))=0? –  msh210 Sep 23 '11 at 16:40
1  
@msh210 hm, I would actually use USING clause (going to update the sql for it): dev.mysql.com/doc/refman/5.5/en/join.html –  Derek Downey Sep 23 '11 at 17:09
2  
@Rolando the problem is, you don't know if A.time is greater or less than B.time. If A.time is smaller, without ABS() you get negatives, and -12,000 < 60 –  Derek Downey Sep 23 '11 at 17:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.