[wp-trac] [WordPress Trac] #40434: Optimize SQL in Admin - In the Post/CPT List `wp-admin/edit.php` > the function get_posts()

[WordPress Trac]

#40434: Optimize SQL in Admin - In the Post/CPT List `wp-admin/edit.php` > the
function get_posts()
----------------------------+-----------------------------
 Reporter:  lriaudel        |      Owner:
     Type:  enhancement     |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Administration  |    Version:  4.7.3
 Severity:  normal          |   Keywords:
  Focuses:  performance     |
----------------------------+-----------------------------
 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
 <?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
 <?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.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/40434>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform