Make WordPress Core

Opened 9 years ago

Closed 3 years ago

Last modified 3 years ago

#34012 closed enhancement (fixed)

post_exists() results in a bad query

Reported by: apokalyptik's profile apokalyptik Owned by: whyisjake's profile whyisjake
Milestone: 5.8 Priority: normal
Severity: normal Version: 4.4
Component: Posts, Post Types Keywords: has-patch needs-testing has-unit-tests needs-dev-note
Focuses: performance Cc:

Description (last modified by SergeyBiryukov)

The current post_exists() function in wp-admin/includes/post.php generates a poorly performing query (makes use of no indexes) This is basically a mirror issue to #33871 for all the same reasons.

The fix is a little bit different because post_date_gmt is actually not indexed and post_date is deep inside of a compound index...

KEY `type_status_date` (`post_type`,`post_status`(1),`post_date`,`ID`),

I have attached a diff to modify post_exists such that it can take post_type and post_status in addition to post_date and so make use of the existing table indexes. In my testing this is almost always 2x as fast in the optimal circumstance and massively faster in exceptionally poor circumstances.

Attachments (4)

post.diff (2.2 KB) - added by apokalyptik 9 years ago.
fix for post_exists() query performance
34012.diff (3.4 KB) - added by MikeHansenMe 9 years ago.
Adds basic tests and includes previous patch
34012.2.diff (2.9 KB) - added by brettshumaker 3 years ago.
Updates patch and adds relevant unit tests.
44314.diff (4.3 KB) - added by peterwilsoncc 3 years ago.

Download all attachments as: .zip

Change History (16)

9 years ago

fix for post_exists() query performance

#1 @DrewAPicture
9 years ago

  • Keywords has-patch needs-unit-tests needs-testing added

We're going to need unit tests for this.

9 years ago

Adds basic tests and includes previous patch

#2 @boonebgorges
9 years ago

In 34680:

Add unit tests for post_exists().

Props MikeHansenMe.
See #34012.

3 years ago

Updates patch and adds relevant unit tests.

#3 @brettshumaker
3 years ago

  • Keywords has-unit-tests added; needs-unit-tests removed

I have an updated patch for this ticket with some relevant unit tests.

I'd really like to see how we can move this forward, large sites using the WordPress Importer plugin would really benefit from this performance fix.

This is my first patch, but please let me know if there's anything else I can do.

#4 @whyisjake
3 years ago

  • Milestone set to 5.8
  • Owner set to whyisjake
  • Status changed from new to accepted

Changes look good to me too. @SergeyBiryukov or @desrosj, any thoughts here?

#5 @SergeyBiryukov
3 years ago

  • Description modified (diff)

3 years ago

#6 @peterwilsoncc
3 years ago

44314.diff is essentially @brettshumaker's patch with the following changes:

  • Added a @since tag to the docblock for the new argument
  • A few coding standards nitpicks fixed in the unit tests
  • Added a couple of tests for post type and post status combos: both happy and unhappy paths

Adding the status parameter to improve the query makes sense to me.

Edit: Sorry about the bad ticket number in the file name.

Last edited 3 years ago by peterwilsoncc (previous) (diff)

This ticket was mentioned in Slack in #core-test by hellofromtonya. View the logs.

3 years ago

This ticket was mentioned in Slack in #core by desrosj. View the logs.

3 years ago

#10 @peterwilsoncc
3 years ago

  • Resolution set to fixed
  • Status changed from accepted to closed

In 51027:

Posts, Post Types: Improve post_exists() query.

Add $status parameter to post_exists() to allow developers to specify a post type, date and status to ensure they hit the wp_posts table's type_status_date index when determining if a post exists.

Props apokalyptik, boonebgorges, brettshumaker, DrewAPicture, MikeHansenMe, peterwilsoncc, whyisjake.
Fixes #34012.

#11 @milana_cap
3 years ago

  • Keywords needs-dev-note added

This ticket was mentioned in Slack in #core-test by hellofromtonya. View the logs.

3 years ago

Note: See TracTickets for help on using tickets.