How to optimize this slow SQL query
Refactoring the query like this gives better performance (from ~7s to ~0.364s on my local):
http://pastebin.com/7VpLGdQB

EXPLAIN now shows this: http://pastebin.com/q8zEkXbx

Joining the node table twice was the bottleneck. The subquery with node and content_type_profile tables is much more efficient. 

On the Drupal side I'll find a way to change this using hook_query_alter or by creating a custom block.