Why not trying the following :
select count(*) from table1,table2 where table1.value = table2.value;
then
select count(*) FROM table1;
select count(*) FROM table2;
You select the min of the two COUNT(*), and then you substract the result of
the first join to the smallest COUNT(*).
I assume this will give you the number of duplicates records ?
Regards,
Jocelyn Fournier
----- Original Message -----
From: "Augey Mikus" <mikus@stripped>
To: "Fournier Jocelyn [Presence-PC]" <joc@stripped>
Cc: <mysql@stripped>
Sent: Wednesday, May 08, 2002 4:39 PM
Subject: Re: Unnecessarily long query time
> Yes. value is actually the primary key in both tables.
>
>
>
> Fournier Jocelyn [Presence-PC] wrote:
>
> >Hi,
> >
> >Are both fields 'value' in table1 and table2 indexed ?
> >
> >Regards,
> > Jocelyn Fournier
> >----- Original Message -----
> >From: "Augey Mikus" <amikus@stripped>
> >To: <mysql@stripped>
> >Sent: Wednesday, May 08, 2002 4:10 PM
> >Subject: Unnecessarily long query time
> >
> >
> >
> >
> >>On one of my MySQL servers (Dell PowerEdge 800Mhz w/ 512MB RAM), I have
> >>a couple tables that have about 3 million records in each. However some
> >>of the records in the tables are duplicates of each other. Here is a
> >>query that should return the number of unique records for both tables:
> >>
> >>select count(*) from table1,table2 where table1.value != table2.value;
> >>
> >>However, this query has been running for more than 40 hours and still
> >>has not finished. Is this normal?
> >>
> >>Thanks,
> >>
> >>Augey
> >>
> >>
> >>
> >>
> >>---------------------------------------------------------------------
> >>Before posting, please check:
> >> http://www.mysql.com/manual.php (the manual)
> >> http://lists.mysql.com/ (the list archive)
> >>
> >>To request this thread, e-mail <mysql-thread108350@stripped>
> >>To unsubscribe, e-mail
> >>
> >>
> ><mysql-unsubscribe-joc=presence-pc.com@stripped>
> >
> >
> >>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >>
> >>
> >>
> >>
> >
> >.
> >
> >
> >
>
>
>
>
