Is there any way to optimize this query (generated by Drupal)?

Question

I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.

The query:

SELECT
  node.nid,
  node.created,
  node.changed,
  node.title
FROM node node
  LEFT JOIN (SELECT td.*,
                    tn.nid AS nid
             FROM taxonomy_term_data td
             LEFT JOIN taxonomy_vocabulary tv ON td.vid = tv.vid
             LEFT JOIN taxonomy_index tn ON tn.tid = td.tid
             WHERE (tv.machine_name IN ('publications'))
             ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
WHERE node.type in ('story')

Explain:

+----+-------------+-------+------------+--------+---------------+-----------+---------+----------------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key       | key_len | ref                  | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+-----------+---------+----------------------+--------+----------+-------------+
|  1 | SIMPLE      | node  | NULL       | ref    | node_type     | node_type | 14      | const                | 427872 |   100.00 | Using where |
|  1 | SIMPLE      | td    | NULL       | index  | NULL          | vid_name  | 771     | NULL                 |    191 |   100.00 | Using index |
|  1 | SIMPLE      | tv    | NULL       | eq_ref | PRIMARY       | PRIMARY   | 4       | dbname.td.vid        |      1 |   100.00 | Using where |
|  1 | SIMPLE      | tn    | NULL       | ref    | term_node     | term_node | 4       | dbname.td.tid        |   5873 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+-----------+---------+----------------------+--------+----------+-------------+

Indexes:

MySQL [dbname]> show indexes from node;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| node  |          0 | PRIMARY          |            1 | nid         | A         |      956206 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          0 | vid              |            1 | vid         | A         |      956206 |     NULL | NULL   | YES  | BTREE      |         |               |
| node  |          1 | node_changed     |            1 | changed     | A         |      749265 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_created     |            1 | created     | A         |      759215 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_frontpage   |            1 | promote     | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_frontpage   |            2 | status      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_frontpage   |            3 | sticky      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_frontpage   |            4 | created     | A         |      714565 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_status_type |            1 | status      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_status_type |            2 | type        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_status_type |            3 | nid         | A         |      956206 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_title_type  |            1 | title       | A         |      503053 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | node_title_type  |            2 | type        | A         |      793978 |        4 | NULL   |      | BTREE      |         |               |
| node  |          1 | node_type        |            1 | type        | A         |           7 |        4 | NULL   |      | BTREE      |         |               |
| node  |          1 | uid              |            1 | uid         | A         |         183 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | tnid             |            1 | tnid        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | translate        |            1 | translate   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | language         |            1 | language    | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| node  |          1 | uuid             |            1 | uuid        | A         |      956206 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MySQL [dbname]> show indexes from taxonomy_term_data;
+--------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| taxonomy_term_data |          0 | PRIMARY       |            1 | tid         | A         |         191 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | taxonomy_tree |            1 | vid         | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | taxonomy_tree |            2 | weight      | A         |         148 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | taxonomy_tree |            3 | name        | A         |         191 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | vid_name      |            1 | vid         | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | vid_name      |            2 | name        | A         |         170 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | name          |            1 | name        | A         |         154 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_term_data |          1 | uuid          |            1 | uuid        | A         |         191 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


MySQL [dbname]> show indexes from taxonomy_vocabulary;
+---------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| taxonomy_vocabulary |          0 | PRIMARY      |            1 | vid          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_vocabulary |          0 | machine_name |            1 | machine_name | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_vocabulary |          1 | list         |            1 | weight       | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_vocabulary |          1 | list         |            2 | name         | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MySQL [dbname]> show indexes from taxonomy_index;
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| taxonomy_index |          1 | term_node |            1 | tid         | A         |         107 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_index |          1 | term_node |            2 | sticky      | A         |         107 |     NULL | NULL   | YES  | BTREE      |         |               |
| taxonomy_index |          1 | term_node |            3 | created     | A         |      484992 |     NULL | NULL   |      | BTREE      |         |               |
| taxonomy_index |          1 | nid       |            1 | nid         | A         |      175997 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

We do not have the ability to change the application, so I'd like to see if this can be improved by adding indexes and/or partitions, but I'm not sure where to start. I think the problem is with the join to the subquery (which alone returns 190k rows - the node table has a bit over 1M records).

Is there any way to fix this?

Edit - added additional information. The aurora cluster has 3 nodes (1 writer, 2 readers, all of which are r5.2xlarge)

MySQL [dbname]> SELECT type,COUNT(1) FROM node GROUP BY type WITH ROLLUP;
+-------------------+----------+
| type              | COUNT(1) |
+-------------------+----------+
| feed              |        1 |
| page              |        1 |
| print_child_story |   446852 |
| run_list          |    51097 |
| story             |   269002 |
| web_child_story   |   197906 |
| wire_story        |    60183 |
| zapier_endpoint   |       20 |
| NULL              |  1025062 |
+-------------------+----------+
9 rows in set (1.21 sec)

MySQL [dbname]> SELECT version() mysql_version,@@global.aurora_version aurora_version,@@global.innodb_buffer_pool_size;
+---------------+----------------+----------------------------------+
| mysql_version | aurora_version | @@global.innodb_buffer_pool_size |
+---------------+----------------+----------------------------------+
| 5.7.12-log    | 2.10.2         |                       3210739712 |
+---------------+----------------+----------------------------------+
1 row in set (0.00 sec)