meta_form inner query is very slow for big post_meta tables

[eddr]

1. This query on every edit page:

   SELECT DISTINCT meta_key 
   FROM wp_postmeta 
   WHERE meta_key NOT BETWEEN '_'
   AND '_z' 
   HAVING meta_key NOT LIKE '\\_%' 
   ORDER BY meta_key 
   LIMIT 30
   

is really expensive.

2. My site has big post_meta table as I'm using lots of custom fields (via advanced custom fields plugin).

3. Removing the filters from the query, in my case, results in the same result set exactly

4. There should be some option for built-in transient/cache of some sort for this specific one. This doesn't change almost at all, so flushing the cache won't be a problem

5. The "order by" part takes really a lot in terms of performance and I'm not sure it is needed really. maybe a filter to allow the developer to change it?

Thanks

[eddr]

Fixing and adding:

1. Fix: My bad, the "order by" is not a performance issue
2. Adding: my post_meta table has almost 200K rows. The query take around 0.3-0.35s on average and is the slowest by far - new VPS with 1 core and 2GB RAM on Linode, only one new site which is not public yet, so I consists 100% of the DB load.

[SergeyBiryukov]

Hi @eddr, welcome to WordPress Trac!

Thanks for the report, we're already tracking this issue in #33885.