[wp-trac] [WordPress Trac] #24498: Improving performance of meta_form()

[WordPress Trac]

#24498: Improving performance of meta_form()
----------------------------+--------------------------
 Reporter:  lumaraf         |       Owner:  pento
     Type:  enhancement     |      Status:  closed
 Priority:  normal          |   Milestone:  4.3
Component:  Administration  |     Version:  3.5
 Severity:  normal          |  Resolution:  fixed
 Keywords:  has-patch       |     Focuses:  performance
----------------------------+--------------------------

Comment (by jeichorn):

 Replying to [comment:29 dd32]:
 > After chatting with @chriscct7 however, there is another angle which we
 could try here.
 >
 > We could perform a query like this:
 > {{{
 > SELECT DISTINCT meta_key
 > FROM wp_postmeta pm
 >     LEFT JOIN wp_posts p ON pm.post_id = p.id
 > WHERE p.post_type IN ( 'post', 'page', 'current-post-type-of-the-post')
 >    AND meta_key NOT BETWEEN '_' AND '_z'
 > HAVING meta_key NOT LIKE '\_%'
 > ORDER BY meta_key DESC
 > }}}
 >
 > I've not benchmarked it yet, but the idea is that plugins which add lots
 of meta_keys probably do it on "hidden" data post_types rather than the
 post_type that a user is actually editing. Since the user hasn't
 consciously added the meta keys to those posts, they shouldn't be shown
 here anyway.
 >
 > If someone has a LOT of posts however (or only posts), this could
 potentially be slower under the conditions.
 >
 > If anyone has a site that fits either of those extremes (thousands of
 meta_keys on non-post post_types - a large WooCommerce site for example I
 believe) or a site with many thousands of posts and can test that query
 out.. it'd be appreciated :)

 That last query fixes the problem until you try to add a post of big type
 (This site has a location database with a couple 100k posts).  The problem
 seems pretty intractable once you do any operation that requires a temp
 table with a couple 100k rows performance collapses.  This effect seems to
 be made worse by utf8mb4.

 At the end of the day this is pretty frustrating.  Fighting a performance
 problems for a feature that spends most of its time hidden from view.
 Maybe just a add a hook for manually setting the meta_key list, and not
 running the query in that case would be a good solution.  Its still a
 landmine waiting to blow up people's sites, but at least people can fix
 it.

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