Finding duplicates between 2 large tables

Question

I have 2 tables, TABLEA and TABLEB.

These contain pretty much the same data, around 90,000 records (30 fields)

There is a field, caller_id that is in both tables.

What I'm trying to do, is to find records in TABLEB that are in TABLEA.

I currently have the following:

SELECT * FROM TABLEB
WHERE caller_id IN (SELECT caller_id FROM TABLEA)

This query is incredibly slow (takes over 2 minutes), which isn't ideal

Is there a way I can possibly speed this up?

Thanks

UPDATE

1   SIMPLE  b   ALL NULL    NULL    NULL    NULL    11719   NULL
1   SIMPLE  a   ALL NULL    NULL    NULL    NULL    11719   NULL

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

Instead of subquery use join check for an index on caller_id

SELECT DISTINCT b.* FROM TABLEB b
INNER JOIN TABLEA a
 ON (a.caller_id =b.caller_id)

edited May 23 '17 at 10:25

Community

answered Feb 07 '14 at 14:14

M Khalid Junaid

Hi. Thanks for that! Although, it is equally as slow in terms of finding records – sipher_z Feb 07 '14 at 14:21
@MKhalidJunaid . . . This could return duplicate values in the result set, if `caller_id` is not unique in `tableA`. – Gordon Linoff Feb 07 '14 at 14:27

Gordon Linoff · Accepted Answer · 2014-02-07T14:36:08.680

0

Your tables are not that big. Try this approach:

SELECT *
FROM TABLEB b
WHERE exists (SELECT 1 FROM TABLEA a where a.caller_id = b.caller_id);

And, be sure there is an index on TABLEA(caller_id):

create index tablea_caller_id on tablea(caller_id);

Some versions of MySQL do a poor job optimizing in with a subquery. The exists clause gets around this.

edited Feb 07 '14 at 14:36

answered Feb 07 '14 at 14:26

Gordon Linoff

Adding the index seems to have speeded up the query!! – sipher_z Feb 07 '14 at 14:43

2 Answers2