Make WordPress Core

Opened 13 years ago

Closed 12 years ago

#18120 closed defect (bug) (fixed)

Slow SQL in get_adjacent_post()

Reported by: javert03's profile javert03 Owned by: ryan's profile ryan
Milestone: 3.5 Priority: normal
Severity: normal Version: 3.2.1
Component: Query Keywords: has-patch commit
Focuses: Cc:

Description

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!

Attachments (4)

link-template.php (72.5 KB) - added by javert03 13 years ago.
includes/link_template.php
link-template.diff (1.4 KB) - added by javert03 13 years ago.
SVN diff
get_adjacent_post.diff (1.1 KB) - added by apokalyptik 12 years ago.
Select p.id and then get_post(). Avoids temp table *and* filesort which occurs on large posts tables where cats are (at least) excluded)
18120.diff (1.4 KB) - added by nacin 12 years ago.

Download all attachments as: .zip

Change History (13)

@javert03
13 years ago

includes/link_template.php

#1 @hakre
13 years ago

Thanks for taking care. Please provide a diff/patch file with the changes you've made, see http://codex.wordpress.org/Reporting_Bugs#Patching_Bugs

@javert03
13 years ago

SVN diff

@apokalyptik
12 years ago

Select p.id and then get_post(). Avoids temp table *and* filesort which occurs on large posts tables where cats are (at least) excluded)

#2 @arpit.tambi.in
12 years ago

I am having this exact same problem, I have over 20 million custom posts in wp_posts table and this query takes over 30 seconds to execute.

#3 @wonderboymusic
12 years ago

  • Milestone changed from Awaiting Review to 3.5

get_adjacent_post.diff looks good

#4 @nacin
12 years ago

  • Keywords commit added

Agreed.

#5 @nacin
12 years ago

Looks like we should not be calling get_post() on '' (if nothing was found).

@nacin
12 years ago

#6 @nacin
12 years ago

18120.diff feels better. Also only now caches a post ID rather than an object.

#7 @ryan
12 years ago

New query.

SQL query: EXPLAIN SELECT p.id FROM wp_trunk_posts AS p WHERE p.post_date < '2012-09-19 18:01:23' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1; 
Rows: 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	range	type_status_date	type_status_date	132	NULL	1	Using where; Using index

Old query.

SQL query: EXPLAIN SELECT p.* FROM wp_trunk_posts AS p WHERE p.post_date < '2012-09-19 18:05:11' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1; 
Rows: 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	range	type_status_date	type_status_date	132	NULL	1	Using where
Version 0, edited 12 years ago by ryan (next)

#8 @ryan
12 years ago

Passes Tests_URL::test_get_adjacent_post()

#9 @ryan
12 years ago

  • Owner set to ryan
  • Resolution set to fixed
  • Status changed from new to closed

In [21919]:

Improve performance of query in get_adjacent_post(). Get the ID and then call get_post(). Props apokalyptik, nacin, javert03. fixes #18120

Note: See TracTickets for help on using tickets.