[EMAIL PROTECTED] wrote:
> > > This is what I wound up going with:
> > >
> > > SELECT b.id,
> > > if(a.a <=> b.a, NULL, b.a),
> > > if(a.b <=> b.b, NULL, b.b),
> > > if(a.c <=> b.c, NULL, b.c),
> > > (NOT (a.a <=> b.a) AND
> > > (a.b <=> b.b) AND
> > > (a.c <=> b.c)) as DiffTest
> > > FROM testC as c
> > > LEFT JOIN testB as b ON c.id = b.id
> > > LEFT JOIN testA as a ON c.id = a.id
> > > HAVING DiffTest = 1
> > >
> > > Your query runs twice as fast as this one, but all of the
> > > if()'s in the SELECT portion allow me to determine which
> > > columns were different between the two tables, which is
> > > important for the next step of the process that I'm
> > > working with.
> > >
...snip...
OK, That's a different assumption than I was working from before. I
thought you wanted to flag the whole row as being different if any field
between them was different or if the row was new. But, then, you said
you needed to detect the diffs. I guess I need to slow down and read
before I respond next time ;-)
Naw, you were working with the info that I'd given. My boiled
down test case was a bit too boiled down, removed some of the
real info that I needed. I need to both flag the row as changed
and figure out what column changed.
My original query was intended to figure out if I'd found a bug
or not. I've filed it in http://bugs.mysql.com waiting to see
what happens next.
Hmmmm..... After re-reviewing your earlier post. I wonder if we don't
need to move the NOT to the outside of the parentheses.... You see, I am
not completely sure of the order of operations... Will the ANDs happen
first then the NOT or will the engine NOT the first term then AND the
NOT-ed results with the other terms.... I am just not sure. If we write
it this way, there can be no doubt:
You're right, typo on my part. The query should read:
SELECT b.id,
if(a.a <=> b.a, NULL, b.a),
if(a.b <=> b.b, NULL, b.b),
if(a.c <=> b.c, NULL, b.c),
(NOT (a.a <=> b.a AND
(a.b <=> b.b AND
(a.c <=> b.c)) as DiffTest
FROM testC as c
LEFT JOIN testB as b ON c.id = b.id
LEFT JOIN testA as a ON c.id = a.id
HAVING DiffTest = 1
Well, I am stumped trying to make it any faster. Maybe changing the
first LEFT JOIN to an INNER JOIN, or caching the results of the first
JOIN in a temp table (splitting up the query into two steps). Other
than that, I think we have it as good as we are going to get it.
Yep, it's running smoothly now. I'd rather do the comparison
in the WHERE clause as it feels cleaner, but this works.
Know what? I just realized we should have been CC:-ing the list this
whole time. That way everyone else would have been able to contribute
and learn, too.
It's a hard call to make, between pestering uninterested parties
and teaching those who never see the intermediate steps in figuring
out a problem. Posting the answers is always a good thing though.
Rene
--
Rene Churchill Specializing in Internet Databases,
Astute Computing, LLC. Custom Programming and
Phone: 802-244-5151 Webmaster support
Fax: 802-244-5512
http://www.astutecomputing.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
