I made a test table with a single varchar field and no index. I loaded it with 30,000 records and an additional 30 duplicate records. I ran the following queries.
Code:
select count(*) FROM tableA
WHERE rowid not in
(SELECT MIN(rowid) FROM tableA GROUP BY column1);
1 hour 12 minutes 58 seconds
Code:
select count(*) FROM tableA A1
WHERE column1 in (SELECT column1 FROM tableA GROUP BY column1 having count(*)
> 1)
and rowid != (select min(rowid) from tableA A2 where A1.column1 = A2.column1);
1 second
Code:
select count(*) from tableA a
where rowid <> ( select max(rowid)
from tableA b
where b.column1 = a.column1);
6 min 29 sec
Your mileage may vary.
