[wp-meta] [Making WordPress.org] #6455: the /view/all-topics view is frequently failing

[Making WordPress.org]

#6455: the /view/all-topics view is frequently failing
----------------------------+-----------------------
 Reporter:  sterndata       |       Owner:  dd32
     Type:  defect (bug)    |      Status:  accepted
 Priority:  high            |   Milestone:
Component:  Support Forums  |  Resolution:
 Keywords:                  |
----------------------------+-----------------------
Changes (by dd32):

 * owner:  (none) => dd32
 * status:  new => accepted


Comment:

 https://wordpress.org/support/view/all-topics/?view=all

 The query behind the main loop on this view is timing out after 30s..
 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
 FROM wp_posts
 WHERE 1=1
 AND wp_posts.post_parent NOT IN (21261,21262,21272)
 AND wp_posts.post_type = 'topic'
 AND ((wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'pending'
 OR wp_posts.post_status = 'trash'
 OR wp_posts.post_status = 'closed'
 OR wp_posts.post_status = 'spam'
 OR wp_posts.post_status = 'archived'
 OR wp_posts.post_status = 'private'))
 ORDER BY wp_posts.ID DESC
 LIMIT 0, 30
 }}}

 If we exclude `?view=all` the query generally completes a bit faster, but
 still not super-fast:
 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
 FROM wp_posts
 WHERE 1=1
 AND wp_posts.post_parent NOT IN (21261,21262,21272)
 AND ((wp_posts.post_type = 'topic'
 AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'closed'
 OR wp_posts.post_status = 'private'
 OR wp_posts.post_status = 'hidden')))
 ORDER BY wp_posts.ID DESC
 LIMIT 0, 30
 }}}

 The post_parent limitation is the plugin/themes/reviews forum id's.

 Removing the pagination functionality (`SQL_CALC_FOUND_ROWS`) would
 probably help greatly here.

 Looking at the `EXPLAIN` for the queries:
 {{{
 id      select_type     table           type    possible_keys
 key                     key_len ref     rows    Extra
 1       SIMPLE          wp_posts        ref
 type_status_date,post_parent    type_status_date        22      const
 8763037 Using index condition; Using where; Using filesort

 id      select_type     table           type    possible_keys
 key                     key_len ref     rows    Extra
 1       SIMPLE          wp_posts        ref
 type_status_date,post_parent    type_status_date        22      const
 8588080 Using index condition; Using where; Using filesort
 }}}
 and by removing the full-row-calc functionality:
 {{{
 id      select_type     table           type    possible_keys
 key     key_len ref     rows    Extra
 1       SIMPLE          wp_posts        index
 type_status_date,post_parent    PRIMARY 8       NULL    47      Using
 where
 }}}

 I think we can just remove `SQL_CALC_FOUND_ROWS` from the query for the
 main support forums, and force it to think there's 50 pages of results,
 that'll take care of the performance issues and not remove any major
 functionality for the view for those who actually want to use it.

-- 
Ticket URL: <https://meta.trac.wordpress.org/ticket/6455#comment:7>
Making WordPress.org <https://meta.trac.wordpress.org/>
Making WordPress.org