WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 21 months ago

#34982 closed enhancement (fixed)

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

Reported by: rodrigosprimo Owned by: johnbillion
Milestone: 4.7 Priority: normal
Severity: normal Version: 4.2
Component: Posts, Post Types Keywords: has-patch commit
Focuses: performance Cc:

Description

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.

Attachments (2)

34982.patch (1.1 KB) - added by rodrigosprimo 2 years ago.
34982.2.patch (1.1 KB) - added by rodrigosprimo 2 years ago.
Refresh patch

Download all attachments as: .zip

Change History (13)

@rodrigosprimo
2 years ago

#1 @johnbillion
2 years ago

  • Keywords has-patch needs-testing added
  • Milestone changed from Awaiting Review to 4.5
  • Owner set to johnbillion
  • Status changed from new to reviewing

This ticket was mentioned in Slack in #core by jorbin. View the logs.


2 years ago

#3 @jorbin
2 years ago

  • Milestone changed from 4.5 to Future Release

#4 @johnbillion
2 years ago

  • Keywords 4.6-early added

#5 @johnbillion
2 years ago

  • Keywords 4.6-early removed
  • Milestone changed from Future Release to 4.6

This ticket was mentioned in Slack in #core by chriscct7. View the logs.


2 years ago

#7 @chriscct7
2 years ago

  • Milestone changed from 4.6 to Future Release

#8 @rodrigosprimo
2 years ago

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
2 years ago

Refresh patch

#9 @johnbillion
21 months ago

  • Milestone changed from Future Release to 4.7

Let's try again :-)

#10 @johnbillion
21 months ago

  • Keywords commit added; needs-testing removed
  • Status changed from reviewing to accepted

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].

#11 @johnbillion
21 months ago

  • Resolution set to fixed
  • Status changed from accepted to closed

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

Note: See TracTickets for help on using tickets.