Slow query in WP_Posts_List_Table, no way to avoid with filters/hooks

[LucasMS]

The post listing page on my wordpress installation is taking more than 15seconds to load (wp_posts w/ more than 120k records, running on an AWS EC2 t2.large instance), and the main cause of this is the following query in the constructor of class wp-admin/includes/class-wp-posts-list-table.php:

SELECT COUNT( 1 )
			FROM $wpdb->posts
			WHERE post_type = %s
			AND post_status NOT IN ( '" . implode( "','", $exclude_states ) . "' )
			AND post_author = %d

It is used just to count the posts the logged-in user posted and show it over the listing table (in "Mine" link).
There is no way to filter this out or enhance the query (other than changing this file).

Suggestion: try to create an database index for this query (I tried, but failed), or enhance it in some way to make it faster.

Other option is to remove this "Mine (xx)" link above the listing, using hooks.

[LucasMS]

Removing the 'mine' index from $views in views_edit-post filter will only remove the link, but the query is still executed.

<?php
add_filter('views_edit-post', 'remove_mine_view');
function remove_mine_view( $views ) {
        unset($views['mine']);
        return $views;
}

[Otto42]

This query is already pretty well optimized using the existing default indexes.

Example:

EXPLAIN SELECT COUNT( 1 ) FROM wp_posts WHERE post_type = 'post' AND post_status NOT IN ( 'draft' ) AND post_author = 1

This comes back as a SIMPLE select using the keys of type_status_date, and post_author, with "Using index condition" and "Using where".

Basically, it already has indexes. The type_status_date is an index on post_type and post_status, with post_date there but not needed in this case. The post_author selection is also indexed by itself (as just post_author).

If you're having issues with it, you may want to check your indexes on wp_posts to make sure they're all there. You can find the default keys on the table definition here:

https://core.trac.wordpress.org/browser/trunk/src/wp-admin/includes/schema.php#L159