update_indexables_author_to_reassigned frequently hangs due to inefficient query

[eddiesshop]

Context: Why was this issue created?

I've had to debug Yoast issues on various sites. From time to time, I use the CLI command wp yoast cleanup to make sure there are no issues with the data I'm trying to index. I've frequently had this command "freeze" or stall on me when it gets to the update_indexables_author_to_reassigned step. When inspecting this step, I arrived at this query. When I run this query manually, many, many, many duplicated rows are returned due to the join statement on the wp_posts table. This causes an equal number of objects to be returned, and consequently causes the step to hang as it tries to work through the duplicate author_id <=> post_author objects.

What is the goal of this issue?

To ensure that only unique pairings of author_id <=> post_author rows are returned for more efficient updating of author => post relationships.

What needs to be done to achieve the goal?

Add a GROUP BY statement.

Does the issue still need UX or research?

Nope.

If available: what are the tips for fixing the problem or possible solutions?

Add a GROUP BY statement.

What is the expected result/behavior?

With much larger data-sets, running wp yoast cleanup should never hang, and this operation should equally as quick as the others involved in the cleanup operation.

Should documentation be added or updated for this change? and if so, where?

Perhaps provide instructions for manually deleting the rows that are returned by this query/command. Like so:

1. Grab the Post IDs returned from the query - SELECT DISTINCT wp_yoast_indexable.object_id FROM wp_yoast_indexable JOIN wp_posts on wp_yoast_indexable.object_id = wp_posts.id WHERE object_type = 'post' AND wp_yoast_indexable.author_id <> wp_posts.post_author ORDER BY wp_yoast_indexable.author_id
2. Remove/delete any rows corresponding to those Post IDs - DELETE FROM wp_yoast_indexable WHERE object_id IN (<Post IDs>)
3. Re-run wp yoast cleanup

[josevarghese]

Hi @eddiesshop

Thank you for submitting the issue and for your PR to help improve the plugin. Our development team will review it and provide further details through your PR.