The post_password column in the _posts table isn’t indexed, which will result in very slow queries with your 70K+ posts. Note that your _posts table probably has at least twice that number of records because WordPress stores all sorts of other data in that table, such as media and menu items, and drafts/revisions/etc.
A temporary solution is to add the missing index to the table. It’s temporary because the index will be removed on every WordPress update. (This action can be blocked, but that means you’ll have to manually implement the database changes in the WP update.)
You’re also using WPML, which may (or may not) be slowing down the query. I’d be curious to see the performance numbers of that query if WPML was deactivated.
And finally, if that query is being generated by WordPress, someone should clean it up. As long as post_password is non-indexed, its WHERE/AND/OR filters should occur last in the chain. In that way MySQL isn’t trying to scan through as many records.
Yes, the query is generated by wordpress with this code:
$args = array(
'post_type' => 'post',
'posts_per_page' => $number,
'title_length' => $title_excerpt_words,
'content_length' => $excerpt_words,
'has_password' => false,
'order' => 'DESC',
);
if ( is_array( $categories ) && count( $categories ) > 0 ) {
$args['category__in'] = $categories;
}
$post = new WP_Query( $args );
I’m thinking the optimal solution would be adding the index on the post_password field and adjusting the generated query.
