5 replaced http://wordpress.stackexchange.com/ with https://wordpress.stackexchange.com/
source | link

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dums_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

Solution edit:

Here is a gist with toscho's solutiontoscho's solution plus a minor fix and two new functions: get_fast_previous_post and get_fast_next_post to get the post objects.

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

Solution edit:

Here is a gist with toscho's solution plus a minor fix and two new functions: get_fast_previous_post and get_fast_next_post to get the post objects.

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

Solution edit:

Here is a gist with toscho's solution plus a minor fix and two new functions: get_fast_previous_post and get_fast_next_post to get the post objects.

4 gist with solution and tweaks
source | link

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

Solution edit:

Here is a gist with toscho's solution plus a minor fix and two new functions: get_fast_previous_post and get_fast_next_post to get the post objects.

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

Solution edit:

Here is a gist with toscho's solution plus a minor fix and two new functions: get_fast_previous_post and get_fast_next_post to get the post objects.

    Tweeted twitter.com/#!/StackWordPress/status/340558480660385792
3 added 460 characters in body
source | link

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

I'm using get_next_post and get_previous_post to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

2 added 11 characters in body
source | link
1
source | link