New algorithm for displaying a hierarchical list of post objects in the WP_Posts_List_Table is incomplete

[rodrigosprimo]

It seems to me that the commit [31730] made to fix #15459 mistakenly left out a small but important part of the proposed patch (https://core.trac.wordpress.org/attachment/ticket/15459/15459.5.diff).

In #15459 a new algorithm was proposed to retrieve from the database only the IDs of the posts and its parents to build the hierarchical list of post objects in the WP_Posts_List_Table. Thus reducing processing time and substantially reducing memory usage on sites with a high number of hierarchical posts.

But the commit didn't included a change in the file src/wp-includes/class-wp.php to add 'fields' to the list of private query vars (see attached patch). Without this change the code is still getting all the data related to all the posts to build the post list table instead of just getting the IDs of the posts and its parents.

[rodrigosprimo]

I spoke with @johnbillion and he asked me to post here the MySQL query to get hierarchical post objects with and without the patch.

Without the patch:

SELECT wp_posts.*
FROM wp_posts
WHERE 1=1
AND wp_posts.post_type = 'page'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'future'
OR wp_posts.post_status = 'draft'
OR wp_posts.post_status = 'pending'
OR wp_posts.post_status = 'private')
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC

With the patch:

SELECT wp_posts.ID, wp_posts.post_parent
FROM wp_posts
WHERE 1=1
AND wp_posts.post_type = 'page'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'future'
OR wp_posts.post_status = 'draft'
OR wp_posts.post_status = 'pending'
OR wp_posts.post_status = 'private')
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC

[rodrigosprimo]

Refresh patch

[johnbillion]

Let's try again :-)

[johnbillion]

Findings:

• This increases the number of queries on the screen by one, but consequently decreases the memory usage, overall query time, and page processing time.
• The more pages you have on your site the greater the memory and time savings.
• All tests remain passing.

In conclusion, yes, this should have been included as part of [31730].

[johnbillion]

In 38451:

Posts, Post Types: Introduce a missing private query var that should have been introduced as part of #15459.

This private query var allows the hierarchical page query on the Pages listing screen to query for wp_posts.ID, wp_posts.post_parent instead of wp_posts.*. This introduces large memory and time savings when the site contains a large number of Pages. Combined with the processing time savings introduced in [31730] this makes the Pages listing screen considerably more performant.

Fixes #34982
Props rodrigosprimo