| Author |
I can't find the missing rows in a table--
|
|
|
| This should be so simple, yet I've struck out.
I have 2 tables, each with a common column called "pid" which is an integer
and is a unique index. There are approx 18 million rows in each table, and
one of the tables has approx 5000 fewer rows than the other table. So it
should be a piece of cake finding the missing rows right?
Well I did a
select * from t1 left join t2 on t1.pid=t2.pid where t2.pid is null
select * from t2 left join t1 on t2.pid=t1.pid where t1.pid is null
and both queries return a null set. I then checked both tables and none of
them have pid as null.
I then counted the number of non-unique pid's and there aren't any (of
course with a unique index I didn't think there would be)
Ok, so there are no rows in t1 that aren't in t2, and vice versa.
There are no duplicate sid values and no empty sid values.
I physically counted the rows in each table and they are indeed off by
around 5000 rows.
I checked the tables for consistency and they passed.
How can anyone explain this? How do I find the missing rows?
TIA
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Mathieu Bruneau 2006-01-01, 7:23 am |
| mos a écrit :
> This should be so simple, yet I've struck out.
>
> I have 2 tables, each with a common column called "pid" which is an
> integer and is a unique index. There are approx 18 million rows in each
> table, and one of the tables has approx 5000 fewer rows than the other
> table. So it should be a piece of cake finding the missing rows right?
>
> Well I did a
>
> select * from t1 left join t2 on t1.pid=t2.pid where t2.pid is null
> select * from t2 left join t1 on t2.pid=t1.pid where t1.pid is null
>
> and both queries return a null set. I then checked both tables and none
> of them have pid as null.
> I then counted the number of non-unique pid's and there aren't any (of
> course with a unique index I didn't think there would be)
>
> Ok, so there are no rows in t1 that aren't in t2, and vice versa.
> There are no duplicate sid values and no empty sid values.
> I physically counted the rows in each table and they are indeed off by
> around 5000 rows.
> I checked the tables for consistency and they passed.
>
> How can anyone explain this? How do I find the missing rows?
> TIA
>
> Mike
>
>
The 2 queries you paste seemed correct and should output the result
unless there is something really strange happening.
If you are using a version that support subquery you could try
select * from t1 where id not in (select id from t2);
Not sure which exactly is suppose to be faster but it's worth a try!
Is it possible that you are hitting some kind of limit on maximum join
number in your server ? I'm not even sure if a limit of that kind exists
(Just putting my tought on the table)
--
Mathieu Bruneau
aka ROunofF
===
GPG keys available @ http://rounoff.darktech.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
|
| At 08:33 PM 1/1/2006, Hank wrote:
>Don't you want the queries to be "outer join" and not "left join"?
A "left join" is a "left outer join".
I ended up using a subselect and that found the missing rows. I'm not sure
why the left join didn't work. I've been using them for years to find
missing rows in tables.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Peter Brawley 2006-01-02, 9:23 am |
| Hank wrote:
>Don't you want the queries to be "outer join" and not "left join"?
??? A left join IS an outer join.
PB
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.9/217 - Release Date: 12/30/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Peter Brawley 2006-01-02, 11:23 am |
| Mike,
>I ended up using a subselect and that found the missing rows.
>I'm not sure why the left join didn't work. I've been using them
>for years to find missing rows in tables.
I think that suggests one of the indexes was munged.
PB
mos wrote:
> At 08:33 PM 1/1/2006, Hank wrote:
>
>
>
> A "left join" is a "left outer join".
> I ended up using a subselect and that found the missing rows. I'm not
> sure why the left join didn't work. I've been using them for years to
> find missing rows in tables.
>
> Mike
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.9/217 - Release Date: 12/30/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|