Create Indexes for Slow Queries

Question

I've gathered that I may be able to solve my slow query problems on a WordPress site with over 200k posts by using indexes. Hoping someone could help explain some.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7,8,14,15,16,18,20,21,30,34,39,54,62,64,86,89,143,146,152,170,213,233,236,238,241,244,246,251,256,263,269,335) ) )
AND ( wp_postmeta.meta_key = 'post_views_count' )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC
LIMIT 0, 20

This is the slow query in question, its calling the post, I vaguely understand what its doing to be honest. I understand what the various tables mean but no idea how I could speed it up.

Here is the explain of said query;

    +------+--------------+------------------------+--------+--------------------------+------------------+---------+-------------------------------------+--------+----------------------------------------------+
| id   | select_type  | table                  | type   | possible_keys            | key              | key_len | ref                                 | rows   | Extra                                        |
+------+--------------+------------------------+--------+--------------------------+------------------+---------+-------------------------------------+--------+----------------------------------------------+
|    1 | PRIMARY      | wp_postmeta           | ref    | post_id,meta_key         | meta_key         | 767     | const                               | 441856 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY      | wp_posts              | eq_ref | PRIMARY,type_status_date | PRIMARY          | 8       | wpdb.wp_postmeta.post_id |      1 | Using where                                  |
|    2 | MATERIALIZED | wp_term_relationships | range  | PRIMARY,term_taxonomy_id | term_taxonomy_id | 8       | NULL                                | 658445 | Using where; Using index                     |
+------+--------------+------------------------+--------+--------------------------+------------------+---------+-------------------------------------+--------+----------------------------------------------+

Obviously it's accessing a huge amount of tables. Is there any way I can create indexes to vastly lower the rows being read?

The previous and the following are seperate but both get_posts() queries.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7,8,14,15,16,18,20,21,30,34,39,54,62,64,86,89,143,146,152,170,213,233,236,238,241,244,246,251,256,263,269,335) ) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

explain;

+------+--------------+------------------------+-------+--------------------------+------------------+---------+------+--------+------------------------------------------+
| id   | select_type  | table                  | type  | possible_keys            | key              | key_len | ref  | rows   | Extra                                    |
+------+--------------+------------------------+-------+--------------------------+------------------+---------+------+--------+------------------------------------------+
|    1 | PRIMARY      | wp_posts              | index | type_status_date         | type_status_date | 180     | NULL | 234322 | Using where; Using index; Using filesort |
|    2 | MATERIALIZED | wp_term_relationships | range | PRIMARY,term_taxonomy_id | term_taxonomy_id | 8       | NULL | 658493 | Using where; Using index                 |
+------+--------------+------------------------+-------+--------------------------+------------------+---------+------+--------+------------------------------------------+

This is from adjacent_post() which is a theme specific query that only moves to posts in the same category (previous/next) SELECT p.ID FROM wp_posts AS p INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2016-11-17 09:22:45' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (54) AND ( p.post_status = 'publish' OR p.post_status = 'private' ) ORDER BY p.post_date DESC LIMIT 1

explain;

+------+-------------+-------+--------+-----------------------------------+------------------+---------+-----------------------------+-------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys                     | key              | key_len | ref                         | rows  | Extra                                        |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-----------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | tt    | const  | PRIMARY,term_id_taxonomy,taxonomy | term_id_taxonomy | 138     | const,const                 |     1 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | tr    | ref    | PRIMARY,term_taxonomy_id          | term_taxonomy_id | 8       | const                       | 67134 | Using index                                  |
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date          | PRIMARY          | 8       | wpdb.tr.object_id |       1 | Using where                                  |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-----------------------------+-------+----------------------------------------------+