[wp-trac] [WordPress Trac] #14035: get_boundary_post() sorts by ID rather than creation date

[WordPress Trac]

#14035: get_boundary_post() sorts by ID rather than creation date
--------------------------+-------------------------------------------------
 Reporter:  jk0           |       Owner:                 
     Type:  defect (bug)  |      Status:  new            
 Priority:  normal        |   Milestone:  Awaiting Triage
Component:  General       |     Version:  3.0.1          
 Severity:  normal        |    Keywords:  has-patch      
--------------------------+-------------------------------------------------

Comment(by tigertech):

 This bug is a more serious problem than it initially appears. It can cause
 performance problems on WordPress sites with thousands of posts.

 The reason is that the final SQL it creates when sorting by ID is:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER
 BY wp_posts.ID ASC LIMIT 0, 1;
 }}}

 However, there is no valid MySQL index on this combination. So MySQL loads
 all the data from the entire wp_posts table where wp_posts.post_type =
 'post' and wp_posts.post_status = 'publish' (which is most of them), then
 does a filesort, then throws away all but one of the rows.

 Here's the MySQL "EXPLAIN" output for one of these queries:

 {{{
 mysql> explain SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER
 BY wp_posts.ID ASC LIMIT 0, 1;
 +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------+
 | id | select_type | table    | type | possible_keys    | key
 | key_len | ref         | rows | Extra                       |
 +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------+
 |  1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date
 | 124     | const,const | 3792 | Using where; Using filesort |
 +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------+
 1 row in set (0.00 sec)
 }}}

 Compare this to the "EXPLAIN" output if the bug is fixed and it correctly
 sorts by post_date:

 {{{
 mysql> explain SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER
 BY wp_posts.post_date ASC LIMIT 0, 1;
 +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-------------+
 | id | select_type | table    | type | possible_keys    | key
 | key_len | ref         | rows | Extra       |
 +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-------------+
 |  1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date
 | 124     | const,const | 3793 | Using where |
 +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-------------+
 1 row in set (0.00 sec)
 }}}

 There's no "filesort" in the correct version (and "filesort" is bad).

 When the wp_posts table isn't in the MySQL or disk cache, I've seen the
 first version take more than 5 seconds occasionally (it shows up in the
 MySQL slow query log).

 So fixing this bug will not only correct the behavior of the link in the
 post header; it will avoid a significant performance issue.

 (The attachment 14035.diff patch appears to be correct, by the way.)

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/14035#comment:5>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software