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

[lriaudel]

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.

[lriaudel]

Class WP_Query, change the get_posts()

[swissspidy]

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.

[johnbillion]

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

[lriaudel]

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]

patch Optimization - SQL - Admin - Post list

[johnbillion]

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.