Slow Query for the Indexable_Post_Indexation_Action->get_query() method

[ovidiul]

• I've read and understood the contribution guidelines.
• I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened.

One of our clients has a large set of posts, over 600K, and upon activating Yoast, the MySQL server seems to be filled with slow queries in the format of

SELECT COUNT(ID)
			FROM wp_posts
			WHERE ID NOT IN (
				SELECT object_id
				FROM wp_yoast_indexable
				WHERE object_type = 'post'
				AND permalink_hash IS NOT NULL
			)
			AND post_type IN ('post', 'page', 'attachment', 'guest-author', 'promo', 'webcafe', 'ispovijesti', 'igre', 'zena')

Please describe what you expected to happen and why.

The average query time for that request is around 28 seconds, and this is considerably slowing down their backend.

Following my comment from here, I did look into an alternative solution and found that this LEFT JOIN query might be more helpful

protected function get_query( $count, $limit = 1 ) {
		$indexable_table = Model::get_table_name( 'Indexable' );
		$post_types      = $this->get_post_types();
		$replacements    = $post_types;

		$select = 'P.ID';
		if ( $count ) {
			$select = 'COUNT(P.ID)';
		}
		$limit_query = '';
		if ( ! $count ) {
			$limit_query    = 'LIMIT %d';
			$replacements[] = $limit;
		}

		return $this->wpdb->prepare(
			"
			SELECT $select
			FROM {$this->wpdb->posts} AS P
			LEFT JOIN $indexable_table AS I ON P.ID = I.object_id AND I.object_type = 'post'
			AND I.permalink_hash IS NOT NULL
			WHERE  I.object_id is Null AND P.post_type IN (" . \implode( ', ', \array_fill( 0, \count( $post_types ), '%s' ) ) . ")
			$limit_query",
			$replacements
		);
	}

Here is a comparison of the queries, the original one takes 28 sec to finish, while the LEFT JOIN one takes about 4 sec, the difference in numbers comes from the wp yoast indexwhich runs in the background, I would guess that might benefit as well.

Happy to make a PR if you consider this. Thanks

How can we reproduce this behavior?

1. Create a large dataset, over 600K posts
2. Try editing a post and to trigger the reindex process

Technical info

• If relevant, which editor is affected (or editors):

• Classic Editor
• Gutenberg
• Classic Editor plugin

• Which browser is affected (or browsers):

• Chrome
• Firefox
• Safari
• Other

Used versions

• WordPress version: 5.6.1
• Yoast SEO version: 15.7
• Gutenberg plugin version:
• Classic Editor plugin version:
• Relevant plugins in case of a bug:

• Tested with theme: custom

[GaryJones]

Just to expand on this - we at WordPress VIP have seen 4-5 instances so far of our customers having 503's that we could trace back to slow queries originating from this indexables table queries. These customers have hundred's of thousands of posts, and the use of a NOT IN query is something we advise against as it doesn't scale well.

[Djennez]

@ovidiul thanks for the report. I've discussed this issue with the architects and they're happy to review a PR if you're willing to make one.

[ovidiul]

Pull request submitted @Djennez