[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:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Query         |     Version:  4.1
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+------------------------------

Comment (by turkeybucket):

 I tracked down the slow query -- it was caused by a modification the theme
 made to the main query with pre_get_posts.  It looks like the modification
 was designed to exclude posts that met one of two conditions based on
 custom fields.  I've included the query below.  I'm guessing this is a
 problem with the theme and not with WordPress.

 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN
 wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  LEFT JOIN
 wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key =
 'story_format' ) WHERE 1=1  AND wp_posts.post_type = 'post' AND
 (wp_posts.post_status = 'publish') AND (
   ( wp_postmeta.meta_key = 'story_format' AND CAST(wp_postmeta.meta_value
 AS CHAR) NOT LIKE '%Long-Form Chapter%' )
   OR
   mt1.post_id IS NULL
 ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 12;

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