Slow SELECT in Drupal CMS
Ouch!  I have never seen ON ( SELECT ... ) = ....  Rewrite that somehow.

Perhaps more importantly, remove LEFT.  You are probably forcing the query start with node, even though it would be much faster to start with a subquery.


  sid != old_sid 


Which column is in which table?  Please use short aliases for all references.

Please provide SHOW CREATE TABLE so we can check that you have adequate indexes.

Please provide EXPLAIN SELECT ... so we can see what the optimizer decided to do.