[wp-trac] [WordPress Trac] #18120: Slow SQL in get_adjacent_post()

[WordPress Trac]

#18120: Slow SQL in get_adjacent_post()
--------------------------+-----------------------------
 Reporter:  javert03      |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  3.2.1
 Severity:  normal        |   Keywords:  has-patch
--------------------------+-----------------------------
 First, thanks for making WordPress what it is. I've gotten a lot of milage
 out of it.

 I help run a blog that gets a few thousand hits per day. I've done some
 optimizing to cut page load times, but keep noticing one particular query
 in the slow_queries log. Something like:
 {{{
 SELECT p.* FROM wp_posts AS p  WHERE p.post_date < '2010-01-25 18:04:53'
 AND p.post_type = 'post' AND p.post_status = 'publish'  ORDER BY
 p.post_date DESC LIMIT 1
 }}}
 The query is made on line 1146 of wp-includes/link-template.php, in the
 function get_adjacent_post(). The query rarely takes more than 2 seconds,
 so it's not an end-of-the-world type of slow, but since it seems to be
 used constantly I'd like to make it faster. On our blog, it usually
 examines 3-4k rows.

 To speed things up, I added an index on the column `post_date`, but an
 EXPLAIN shows that the index is not being used. For whatever reason, use
 of the comparison operators causes MySQL to not use an index. Replacing
 comparison operators with BETWEEN...AND allows the key to be used.
 {{{
 ...  WHERE p.post_date BETWEEN '2008-03-09 09:00:00' AND '9999-12-31
 00:00:00' ...
 }}}
 However, WordPress also appears to only be using ID, post_title,
 post_status, post_date from the adjacent posts. So I made the changes in
 the attached link_template.php. I changed 3 lines and marked each line
 with my username, javert03.

 Thanks!

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/18120>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software