WordPress.org

Make WordPress Core

Opened 3 years ago

Last modified 21 months ago

#40434 new enhancement

Optimize SQL in Admin - In the Post/CPT List `wp-admin/edit.php` > the function get_posts()

Reported by: lriaudel Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.7.3
Component: Query Keywords: has-patch needs-testing
Focuses: administration, performance Cc:
PR Number:

Description

When we list a post, a page or a custom-post-type the default query is

SELECT SQL_CALC_FOUND_ROWS en_posts.ID
FROM en_posts 
WHERE 1=1 
AND en_posts.post_type = 'post'
AND (en_posts.post_status = 'publish'
OR en_posts.post_status = 'moderation'
OR en_posts.post_status = 'refusal'
OR en_posts.post_status = 'future'
OR en_posts.post_status = 'draft'
OR en_posts.post_status = 'pending'
OR en_posts.post_status = 'private') 
ORDER BY en_posts.post_date DESC
LIMIT 0, 20

This query cause slow queries. The operation OR is not appropriate and not optimize.
It is better to use the operation IN.

I propose to change lines in the clasw-wp-query.php line 2348:

Today :

<?php
                } elseif ( !$this->is_singular ) {
                        $where .= " AND ({$wpdb->posts}.post_status = 'publish'";

                        // Add public states.
                        $public_states = get_post_stati( array('public' => true) );
                        foreach ( (array) $public_states as $state ) {
                                if ( 'publish' == $state ) // Publish is hard-coded above.
                                        continue;
                                $where .= " OR {$wpdb->posts}.post_status = '$state'";
                        }

                        if ( $this->is_admin ) {
                                // Add protected states that should show in the admin all list.
                                $admin_all_states = get_post_stati( array('protected' => true, 'show_in_admin_all_list' => true) );
                                foreach ( (array) $admin_all_states as $state ) {
                                        $where .= " OR {$wpdb->posts}.post_status = '$state'";
                                }
                        }

                        if ( is_user_logged_in() ) {
                                // Add private states that are limited to viewing by the author of a post or someone who has caps to read private states.
                                $private_states = get_post_stati( array('private' => true) );
                                foreach ( (array) $private_states as $state ) {
                                        $where .= current_user_can( $read_private_cap ) ? " OR {$wpdb->posts}.post_status = '$state'" : " OR {$wpdb->posts}.post_author = $user_id AND {$wpdb->posts}.post_status = '$state'";
                                }
                        }

                        $where .= ')';
                }

The proposition :

<?php
                } elseif ( !$this->is_singular ) {
                        $where_status[] = 'publish';

                        // Add public states.
                        $public_states = get_post_stati( array('public' => true) );
                        foreach ( (array) $public_states as $state ) {
                                if ( 'publish' == $state ) // Publish is hard-coded above.
                                        continue;
                                $where_status[] = $state;
                        }

                        if ( $this->is_admin ) {
                                // Add protected states that should show in the admin all list.
                                $admin_all_states = get_post_stati( array('protected' => true, 'show_in_admin_all_list' => true) );
                                foreach ( (array) $admin_all_states as $state ) {
                                        $where_status[] = $state;
                                }
                        }

                        if ( is_user_logged_in() ) {
                                // Add private states that are limited to viewing by the author of a post or someone who has caps to read private states.
                                $private_states = get_post_stati( array('private' => true) );
                                foreach ( (array) $private_states as $state ) {
                                        $where_status[] = $state;
                                        $where_post_author = current_user_can( $read_private_cap ) ? "" : " OR {$wpdb->posts}.post_author = $user_id";
                                }
                        }
                        $where .= " AND ( {$wpdb->posts}.post_status IN ('" . implode("','",$where_status) . "')";
                        $where .= $where_post_author;
                        $where .= ')';
                }

And the result is better :

SELECT SQL_CALC_FOUND_ROWS en_posts.ID
FROM en_posts 
WHERE 1=1 
AND en_posts.post_type = 'post'
AND ( en_posts.post_status IN ('publish','moderation','refusal','future','draft','pending','private')) 
ORDER BY en_posts.post_date DESC
LIMIT 0, 20

It would be interesting to do the same work on other parts of get_posts where the OR is used.

Attachments (2)

class-wp-query.zip (24.1 KB) - added by lriaudel 3 years ago.
Class WP_Query, change the get_posts()
40434-optimize-sql-admin-post-list.diff (6.3 KB) - added by lriaudel 3 years ago.
patch Optimization - SQL - Admin - Post list

Download all attachments as: .zip

Change History (8)

@lriaudel
3 years ago

Class WP_Query, change the get_posts()

#1 @swissspidy
3 years ago

Hey there,

Thanks for your contribution!

Would you mind uploading your changes in a proper patch format instead of a ZIP file? See https://make.wordpress.org/core/handbook/tutorials/working-with-patches/ for more information.

#2 @SergeyBiryukov
3 years ago

  • Component changed from Administration to Query
  • Focuses administration added

#3 follow-up: @johnbillion
3 years ago

It would also be great to see some before/after query timings for this change.

#4 in reply to: ↑ 3 ; follow-up: @lriaudel
3 years ago

Replying to johnbillion:

It would also be great to see some before/after query timings for this change.

If you want info of the difference OR and IN, here :
http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance?answertab=votes#tab-top

And here :
http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause

@lriaudel
3 years ago

patch Optimization - SQL - Admin - Post list

#5 in reply to: ↑ 4 @johnbillion
3 years ago

  • Keywords has-patch needs-testing added

Replying to lriaudel:

If you want info of the difference OR and IN, here :
http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance?answertab=votes#tab-top

And here :
http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause

Right, but actual numbers are still needed to demonstrate there's an improvement. What improvements did you see with your testing?

Thanks for the patch! You might want to remove the unrelated changes to make it clearer what's changed.

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


21 months ago

Note: See TracTickets for help on using tickets.