SGreen@stripped 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
