Mysql – large SQL Performing quite slow (roughly 2 seconds for 290 rows) when having to deal with entries that can be NULL

MySQLperformancequery-performance

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

                 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 )


                 SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS x 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id

And the entire query will be

SELECT  xyx.version_stamp, xyx.pscdesc_id, xyx.psc, xyx.pscdesctext 
FROM    pscdesc_history AS xyx
JOIN    pscdesc_history AS xyy
ON  (xyx.psc IN (SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id) 
     OR xyx.psc IS NULL)
AND xyy.pscdesc_id = xyx.pscdesc_id
AND  xyy.psc IN ( SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id)

This must work twice farster I think, check the indexes also.