[wp-trac] [WordPress Trac] #30814: Large wp_postmeta table causes core database queries to become extremely slow.

[WordPress Trac]

#30814: Large wp_postmeta table causes core database queries to become extremely
slow.
--------------------------+--------------------------
 Reporter:  turkeybucket  |       Owner:
     Type:  defect (bug)  |      Status:  closed
 Priority:  normal        |   Milestone:
Component:  Query         |     Version:  4.1
 Severity:  normal        |  Resolution:  invalid
 Keywords:                |     Focuses:  performance
--------------------------+--------------------------
Changes (by boonebgorges):

 * status:  new => closed
 * resolution:   => invalid
 * milestone:  Awaiting Review =>


Comment:

 Thanks again for digging up the implementation details.

 I've run some tests between 4.0 and 4.1. The queries are almost identical,
 except that in 4.1 all of the JOINS are LEFT JOINS:

 4.0: `INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) LEFT
 JOIN wp_postmeta AS mt1 ...`
 4.1: `LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT
 JOIN wp_postmeta AS mt1 ...`

 This change dates from [29890]. See #29062 for the back story. In short,
 mixing INNER JOINs with LEFT JOINs when using NOT EXISTS was leading to
 incorrect results. It's true (as noted in the ticket) that the change to
 LEFT JOINs means a performance hit - especially in cases where the
 postmeta table is filled with a bunch of irrelevant data. This is what you
 experienced, and that's why it went away when you cleaned up the old
 plugin data. But this is not something we can roll back without
 reintroducing the bug.

 Depending on your data set, you may be able to write a version of the
 'pre_get_posts' filter that performs better by doing separate direct
 queries against postmeta to get your post_ids - one for the NOT LIKE and
 one for the NOT EXISTS - then joining the results with `array_merge` and
 passing them as a `post__in` param to `WP_Query`.

 See #30044 for a discussion of using subqueries for `WP_Meta_Query`
 itself. Thanks again for the report.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/30814#comment:9>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform