How to optimize this slow SQL query

I have a slow SQL query on my Drupal website and I don't know how to optimize it.

Here is the query that Views produces: http://pastebin.com/StAEiXbY

SQL fiddle: http://sqlfiddle.com/#!2/20081/1/0

EXPLAIN shows this: http://pastebin.com/uKtte5KR

Amount of data (rows):

+-------+----------------------+------------+----------------+-------------+-----------+
| node  | content_type_profile | userpoints | node_revisions | node_access | term_node |
+-------+----------------------+------------+----------------+-------------+-----------+
| 59805 |                23294 |       2692 |          71284 |      147783 |    225052 |
+-------+----------------------+------------+----------------+-------------+-----------+

This query takes more than 10s to complete. How can I optimize this 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.

StackExchange Database Administrators Q#73583, answer score: 2