I have run mysqldumpslow and found this query as very slow (to much rows):

Can someone please look into it optimizing it? The sub-query seems to be the cause.