3

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.

3

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;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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