SELECT version_stamp,
pscdesc_id,
psc,
pscdesctext
FROM pscdesc_history
WHERE ( psc IN (SELECT psc
FROM pso_history AS c
WHERE ( c.project = 121
OR c.project IS NULL )
AND EXISTS (SELECT * FROM pso_history AS x
WHERE x.project = 121
AND x.pso_id = c.pso_id ))
OR psc IS NULL )
AND EXISTS (SELECT * FROM pscdesc_history AS xyy
WHERE xyy.psc IN (SELECT psc
FROM pso_history AS c
WHERE ( c.project = 121
OR c.project IS NULL )
AND EXISTS (SELECT * FROM pso_history AS x
WHERE x.project = 121
AND
x.pso_id = c.pso_id))
AND xyy.pscdesc_id = pscdesc_history.pscdesc_id )
Maybe in prosa what I am trying to do:
Entries in the pscdesc_history
table represent changes. Each entry is a change.
so a record with pscdesc_id
can get changed, say pscdesctext
gets changed. That gives us a new entry with the same pscdesc_id
but a new version_stamp
and pscdesctext
the rest can be empty.
To which project these entries (crucial, since I only want to deal with one project) belong, I don't know. I have to look at the table pso_history
. psc
is the foreign key to a third table.
Why don't I just join? Since this is a table full of records of changes, the actual foreign key psc
might be null. The same applies for pso_history
where the forgein key project
might be null too.
So i have to get all records of pscdesc_history
that are related to a record in pso_history
with a pso_history.project
of 121 or that are related to a record with pso_history.project
that was at some point 121.
Is there something you can see where you would think I could speed up things ? replace the AND EXISTS
with something else?
Best Answer
Use DISTINCT everywhere you can and I don't understand why not to join instead of correlating
And the entire query will be
This must work twice farster I think, check the indexes also.